0

I have two database tables called Item and Property:

Item
--------
item_id
title

Property
--------
property_id
item_id
property_value

Each Item can be associated with any number of Property entries.

Is there an SQL/MySql query that could return Item data with its corresponding Property data within the same row? (i.e. I'd like a query to return all data from these tables, formatted such that each row of the returned result contains all Item and related Property data of a particular item)

Is this possible?

user1752396
  • 35
  • 4

2 Answers2

1

If you don't mind the results being in one column, you can do something like:

SELECT i.item_id,
       group_concat(p.property_id, ':', p.property_value separator ';') as properties
FROM Item i INNER JOIN
     Property p
     ON i.item_id = p.item_id
GROUP BY i.item_id;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

Looking for this?:

SELECT *
FROM Item i INNER JOIN Property p
ON i.item_id = p.item_id

This will project item_id from both tables. If you want to limit the columns, list them individually from each table.

VBlades
  • 2,241
  • 1
  • 12
  • 8
  • I do not think so. It seems OP wants dynamic `PIVOT`. – PM 77-1 Jun 15 '14 at 01:05
  • Thanks for the quick reply, VBlades! Unfortunately, I don't think this will work from me... I'd like all Property data for a given Item to be returned within the same row of the result. (i.e. If I had 3 Property entries associated with the same Item entry, I think the above query would return 3 separate rows for it instead of combining them into one) – user1752396 Jun 15 '14 at 01:07
  • Yes, sorry, my mistake, read it wrong. Will think about it. Apologies. – VBlades Jun 15 '14 at 01:17
  • @user1752396: PM 77-1 is correct, I think, generally this would be a PIVOT solution. There is a solution here, but looks involved: http://stackoverflow.com/questions/7674786/mysql-pivot-table. Must the data be in columns? Or would one field with all the data delimited by a character be ok? – VBlades Jun 15 '14 at 01:21