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
Display two related SQL results in a row
Moderator: General Moderators
-
markjwiggins
- Forum Newbie
- Posts: 3
- Joined: Tue May 12, 2009 1:14 pm
- 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
What does the SQL query look like which produces these, undesired, results.markjwiggins wrote: enclosureID orderNo controlID
enclosureID orderNo controlID
-
markjwiggins
- Forum Newbie
- Posts: 3
- Joined: Tue May 12, 2009 1:14 pm
Re: Display two related SQL results in a row
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?
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?
- 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
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
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
Thanks