MySQL: How to join three tables in one query with an assign table?

Question

I have three tables:

ITEMS
itemid | itemname
1      | Item 1

FEATURES
featureid | featurename
1         | feature1
2         | feature2
3         | feature3
4         | feature4
...       | ...

ASSIGN
assignid | itemid | featureid
1        | 1      | 1
1        | 1      | 2
1        | 1      | 3
1        | 1      | 4

I want one query that will give me a result similar to this:

ITEMS
itemid | itemname | featurename1 | featurename2  | etc
1      | Item 1   | feature1     | feature2      | etc

Is there a way to do this without having a second query? I swear I'm just thinking about it in the wrong way? Any ideas?

Note: features could be anything from 0 to 50 different features. Is my database design perhaps wrong?

[see this perhaps useful](http://stackoverflow.com/questions/5196371/sql-query-concatenating-results-into-one-string) — mojtaba, Apr 25 '13 at 18:52
If an item only has eg. featureid 3 and 4 assigned to it (but not 1 or 2) then do you want its features to appear in columns `featurename3` and `featurename4` (to match the featureid) or in columns `featurename1` and `featurename2` (ie. to fill the columns from left to right)? — , Apr 25 '13 at 19:00

Miguel-F · Accepted Answer · 2013-04-25T19:00:21.650

2

Link them using your ASSIGN table and then you can use GROUP_CONCAT() to do what you want. Try this:

SELECT
  ITEMS.itemid,
  ITEMS.itemname,
  FEATURES.featureid,
  GROUP_CONCAT(FEATURES.featurename) AS FEATURES
FROM ITEMS
 JOIN ASSIGN ON ITEMS.itemid = ASSIGN.itemid
 JOIN FEATURES ON FEATURES.featureid = ASSIGN.featureid

SQL Fiddle link

edited Apr 25 '13 at 19:00

answered Apr 25 '13 at 18:55

Miguel-F

13,450
6
38
63

score 0 · Answer 2 · answered Apr 25 '13 at 18:47

Just use your ASSIGN table as a cross reference for ITEMS and FEATURES. So join everything together through the foreign keys in the ASSIGN table like so:

SELECT
  ITEMS.itemid
, ITEMS.itemname
, FEATURES.featureid
, FEATURES.featurename
, ASSIGN.assignid
FROM `ITEMS`
 JOIN `ASSIGN`
   ON ITEMS.itemsid = ASSIGN.itemid
 JOIN `FEATURES`
   ON FEATURES.featureid = ASSIGN.featureid

MySQL: How to join three tables in one query with an assign table?

2 Answers2