MySQL: How to fetch data with left-join if column contains multiple ids?

Question

Assuming the following:

Table "info":

 id | target_ids
----|------------
 1  | 2
 2  | 
 3  | 4,1
 4  | 2,3,1

Table "targets":

 id | value
----|------------
 1  | dog
 2  | cat
 3  | tiger
 4  | lion

Using left join, I'm expecting something like this:

 id | target_ids | value
----|---------------------
 1  | 2          | cat
 2  |            | 
 3  | 4,1        | lion,dog
 4  | 2,3,1      | cat,tiger,dog

I've tried this:

select info.*, targets.value from info left join targets on info.target_ids = targets.id

The results I got is single values in "value" column

 id | target_ids | value
----|---------------------
 1  | 2          | cat
 2  |            | 
 3  | 4,1        | lion
 4  | 2,3,1      | cat

How can I get results as it's showing in the 3rd table? Thanks

I would say your tables are malformed. Your values are not atomic (see 1NF). Anyway: If you cannot change the tables you could create a temporary table from the data in the info-table and your the temp-table for the join. — konqi
– konqi, Commented May 30, 2012 at 8:53

eggyal · Accepted Answer · 2012-05-30 09:05:28Z

You need to use MySQL's FIND_IN_SET() function as the join criterion:

SELECT   info.*, GROUP_CONCAT(targets.value) AS value
FROM     info LEFT JOIN targets ON FIND_IN_SET(targets.id, info.target_ids)
GROUP BY info.id

See it on sqlfiddle.

However, you would probably be best to normalise your data structure and store your info-target relations in a separate table:

CREATE TABLE InfoTargets (
  InfoID   INT NOT NULL,
  TargetID INT NOT NULL,
  PRIMARY KEY (InfoID, TargetID),
  FOREIGN KEY (InfoID)   REFERENCES info    (id),
  FOREIGN KEY (TargetID) REFERENCES targets (id)
);

INSERT INTO InfoTargets VALUES
  (1,2),
  (3,4), (3,1),
  (4,2), (4,3), (4,1);

ALTER TABLE Info DROP COLUMN target_ids;

Then you would do:

SELECT   info.id,
         GROUP_CONCAT(targets.id)    AS target_ids,
         GROUP_CONCAT(targets.value) AS value
FROM     InfoTargets
  LEFT JOIN info    ON   InfoID = InfoTargets.InfoID
  LEFT JOIN targets ON TargetID = InfoTargets.TargetID
GROUP BY info.id

If the order of targets is important (and might differ between each info item), you would need to create an additional rank column in InfoTargets.

Collectives™ on Stack Overflow

MySQL: How to fetch data with left-join if column contains multiple ids?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related