Page 1 of 1

Display two related SQL results in a row

Posted: Tue May 12, 2009 1:24 pm
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

Re: Display two related SQL results in a row

Posted: Tue May 12, 2009 2:01 pm
by jayshields
markjwiggins wrote: enclosureID orderNo controlID
enclosureID orderNo controlID
What does the SQL query look like which produces these, undesired, results.

Re: Display two related SQL results in a row

Posted: Tue May 12, 2009 2:17 pm
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?

Re: Display two related SQL results in a row

Posted: Tue May 12, 2009 4:43 pm
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.

Re: Display two related SQL results in a row

Posted: Tue May 12, 2009 4:52 pm
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