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?

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)? — user359040
– user359040, Commented Apr 25, 2013 at 19:00

Miguel-F · Accepted Answer · 2013-04-25 19:00:21Z

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, 2013 at 19:00

answered Apr 25, 2013 at 18:55

Miguel-F

13.6k6 gold badges40 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Cory Shaw · Accepted Answer · 2013-04-25 18:47:46Z

0

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

answered Apr 25, 2013 at 18:47

Cory Shaw

1,13010 silver badges16 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related