SQL combining Rows in Query

Question

I am using a MySQL DB and I'm trying to combine my results. my SQL Chops aren't experienced enough to come up with a way to accomplish this.

the Picture explains my objective and db structure. https://i.sstatic.net/x6dCo.png

this is what I was Using:

SELECT
      LISTITEMS.ID,
      LISTITEMS.ItemName,
      PROPVALUE.PropValue,
      LISTPROPERTIES.PropName
FROM LISTITEMS
INNER JOIN PROPVALUE
   ON LISTITEMS.ID = PROPVALUE.ItemID
INNER JOIN LISTPROPERTIES
   ON LISTPROPERTIES.ID = PROPVALUE.PropID
WHERE LISTITEMS.ListID = '$_GET[ID]'

this Returns 2 rows and I need 1 row (I edited out the 'ID' columns from the other 2 tables).

ID  ItemName  PropValue    ListID       PropName
1   item 1    PropValue1        1       Property1
1   item 1    PropValue2        1       Property2

Edit Desired output:

ID  ListID ItemName  PropName1 PropValue1   PropName2 PropValue2
1   1      Item 1    Property1 PropValue1   Property2 PropValue2

How should they be combined? PropValue and PropName differ. Please post an example of how you want the output to look. — Michael Berkowski
– Michael Berkowski, Commented Feb 17, 2012 at 16:33
(By the way, we assume you have already verified that $_GET['ID'] is a proper integer. Otherwise, your query is vulnerable to SQL injection...) — Michael Berkowski
– Michael Berkowski, Commented Feb 17, 2012 at 16:35
Wait, I get it after looking at the image more closely. Does what I added above match your intent? — Michael Berkowski
– Michael Berkowski, Commented Feb 17, 2012 at 16:36
There needs to be a button where you can mark a question "Vulnerable to SQL Injection" without writing a comment. =) — JohnFx
– JohnFx, Commented Feb 17, 2012 at 16:36
@JordanR - Is it possible that there might be more than 2 value rows for a given property? IE. Does this need to add PropName3 and PropValue3 as needed? — JohnFx
– JohnFx, Commented Feb 17, 2012 at 16:41

Michael Berkowski · Accepted Answer · 2012-02-17 16:40:29Z

Since the property names will differ dynamically, it is going to be far easier to combine them in a GROUP_CONCAT() and then separate them in your application layer:

Edit: Fixed query to include all your JOINs:

SELECT
      LISTITEMS.ID,
      LISTITEMS.ItemName,
      GROUP_CONCAT(CONCAT_WS('|', LISTPROPERTIES.PropName, PROPVALUE.PropValue)) AS Properties
FROM LISTITEMS
INNER JOIN PROPVALUE
   ON LISTITEMS.ID = PROPVALUE.ItemID
INNER JOIN LISTPROPERTIES
   ON LISTPROPERTIES.ID = PROPVALUE.PropID
WHERE LISTITEMS.ListID = '$_GET[ID]'
GROUP BY LISTITEMS.ID, LISTITEMS.ItemName

This will output something like:

ID ItemName  Properties
1  Item1     Property1|PropValue1,Property2|PropValue2,Property3|PropValue3

Then in your PHP code, explode() the properties first on , to get the pairs, then explode() each of those on | to get the name|value key & value.

Collectives™ on Stack Overflow

SQL combining Rows in Query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related