get data and all related ids

Hi all,

I am redesigning my database and I seem to run into a problem.

There are 2 tables:
Table ITEM - information about individual items
Table SET - items can be grouped into sets; each set has additional properties like name, description, stamp, etc.

(In the past, I had a field in the SET table, where I listed ids of the individual items as a comma-separated string.)

Now, I have a field in the ITEM table that holds the id of the SET the item belongs to.

In my queries, I often need to get properties of a SET and ids of the ITEMs belonging to a set.

I know I can retrieve the set properties in one query and then get the items belonging to a set in another query, but it gets complicated when I need information about more than one set. The number of queries grows too fast.

My question: Can I somehow retrieve SET properties + IDs of ITEMs in a single query, preferably obtain the ids in a single field "ID1,ID2,ID3..."

Thanks in advance

 

it would be better to understand the problem if you can post table structure and at least 4 - 5 entries in both tables.

it will help us test out queries as well

by the way, is it mysql or other database?

 

Yes, you can do that in a single query (if you are using MySQL, i don't know about other DBs).

SELECT `SET`.*, GROUP_CONCAT(`ITEM`.set_id) FROM `SET` 
INNER JOIN `ITEM` ON `ITEM`.set_id = `SET`.id
WHERE `SET`.id = 1
GROUP BY `SET`.id

Code (markup):

I recommend you not to use table name SET, because it is a reserve word in mysql. Change this to something else to avoid confusion otherwise you have always to use backticks (`) around the table name as I used above.