CODEWITHSUNDEEP
mysqlsqldatabaseone-to-manymany-to-one
user1752396
user1752396

Reputation: 35

SQL query needed to combine many-to-one values into single result

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?

Upvotes: 0

Views: 669

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269603

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;

Upvotes: 1

VBlades
VBlades

Reputation: 2251

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.

Upvotes: 0

Related Questions