Display two related SQL results in a row

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
markjwiggins
Forum Newbie
Posts: 3
Joined: Tue May 12, 2009 1:14 pm

Display two related SQL results in a row

Post by markjwiggins »

Hi,

I am trying to display some results from an SQL query in the form of a table.

The situation:
I have two tables linked by an id ie
Table 1: enclosureID(key), orderNo
Table 2: controID(key), enclosureID

Where the two enclosureID's and related with 1-Many (table1 - table2)

For every table1.enclosureID the is two controlID entries.
I want to display the results when I join these two tables together, which I can do, but I want to display it like:

enclosureID orderNo controlID controlID

and not:

enclosureID orderNo controlID
enclosureID orderNo controlID

So basically I want the results to come back on one line with one enclosureID having two controlID's rather than having two rows per enclosure.

Has anyone got any way of doing this?
I hope I have explained the situation well enough. Thanks in advance.

//mark
User avatar
jayshields
DevNet Resident
Posts: 1912
Joined: Mon Aug 22, 2005 12:11 pm
Location: Leeds/Manchester, England

Re: Display two related SQL results in a row

Post by jayshields »

markjwiggins wrote: enclosureID orderNo controlID
enclosureID orderNo controlID
What does the SQL query look like which produces these, undesired, results.
markjwiggins
Forum Newbie
Posts: 3
Joined: Tue May 12, 2009 1:14 pm

Re: Display two related SQL results in a row

Post by markjwiggins »

I have only used simple sql to achieve the undesired result:

SELECT e.enclosureID, e.orderID, c.controlID
FROM tblEnclosure AS e
JOIN tblController AS c
ON e.enclosureID = c.enclosureID";

If I add GROUP BY e.enclosureID it gives one line per enclosureID but emits one control.
I am pretty new to sql so wondered if it was possible to add any conditions so that if two lines appear with the same enclosureID then take the controlID and make a new temp column called controlID2 or similar?
User avatar
jayshields
DevNet Resident
Posts: 1912
Joined: Mon Aug 22, 2005 12:11 pm
Location: Leeds/Manchester, England

Re: Display two related SQL results in a row

Post by jayshields »

I'm not sure how to do what you're suggesting in SQL alone, but you can stick with your existing query and re-format the results quite easily in PHP.
markjwiggins
Forum Newbie
Posts: 3
Joined: Tue May 12, 2009 1:14 pm

Re: Display two related SQL results in a row

Post by markjwiggins »

Ok thanks, I just didn't know if SQL encorporated conditions in anyway, but I will crack on with the php to achieve what I need!
Thanks
Post Reply