Mysql JOIN with IN and multiple results of RIGHT table

Question

The LEFT table (simplified) is:

id
name (ex:Bob)
tags (ex:1,4,6)

The RIGHT table is:

id
tag (ex:Sailing)

I want to get the results of the LEFT table like this:

row 1:
   name: Bob
   tags: Sailing,Snowboard,...

The closest I got was there:

SELECT `name`, GROUP_CONCAT(`right`.`tag`) AS tags
FROM (`left`)
LEFT OUTER JOIN `right` ON `right`.`id` IN(left.tags)
ORDER BY `name` asc

But this gives me only one row because of the GROUP_CONCAT(). Whithout it, it gives me all the results but only with the first tag in the comma-separated list. I know I'm close but I've been losing too much time on this. Any help appreciated.

I know I can create a third table left_right that would contain the rows (id, left_id, right_id) to make it work, but I would like to avoid that.

`tags` is an integer field? (You can have several Bob rows with different tags?) — LSerni, Sep 20 '12 at 18:34
You cannot really avoid that, it will get even messier in the future. If you are using relational databases, try to use them right. You need to create a third table. — kapa, Sep 20 '12 at 18:34
tags is a VARCHAR with comma-separated numbers. There is only one "Bob" row with all his tags. I totally agree with you bažmegakapa, especially to make it simpler if we want to delete a tag from a user in the future, but the other person I'm working with who is coding the database insertion is not that good with mysql so I'm trying to make it simple for him. — NaturalBornCamper, Sep 20 '12 at 18:38
Ouch. Then you want to *translate* the tags from numbers to texts? I'll edit my answer. It gets more complicated, though. — LSerni, Sep 20 '12 at 18:43
Yep, I know, if it's too much I'll help my team-mate and create that third table — NaturalBornCamper, Sep 20 '12 at 18:57

LSerni · Answer 1 · 2012-09-20T18:52:14.863

First of all what bažmegakapa said is correct, and more. If I understood correctly the setup you described, you are already wasting considerable space (and performances).

You can do this:

CREATE TABLE pleft ( id integer, name varchar(20), tags integer );
CREATE TABLE pright ( id integer, tag varchar(20));

INSERT INTO pleft VALUES ( 1, 'Bob', 1 ), ( 9, 'Bob', 4 ), ( 15, 'Bob', 6 );
INSERT INTO pleft VALUES ( 2, 'Ann', 1 ), ( 3, 'Joe', 4 ), ( 4, 'Joe', 6 );

INSERT INTO pright VALUES ( 1, 'Sailing' ), ( 4, 'Snowboarding' ), ( 6, 'Skiing' );


SELECT pleft.name, GROUP_CONCAT(pright.tag)
    FROM pleft JOIN pright ON ( pleft.tags = pright.id )
    GROUP BY pleft.name ORDER BY pleft.name;

+------+-----------------------------+
| name | GROUP_CONCAT(pright.tag)    |
+------+-----------------------------+
| Ann  | Sailing                     |
| Bob  | Sailing,Skiing,Snowboarding |
| Joe  | Snowboarding,Skiing         |
+------+-----------------------------+

...but notice how the names are needlessly duplicated in each row of the pleft table. Ideally you would have one table modeling the person: (id=1, name="Bob"), one table modeling the tags (id=6, value="Skiing") and one table containing their relation. This would ensure that, e.g., Bob decides to go by "Robert", you don't have to de-bob the whole tags table, but only the one row involving Bob.

UPDATE

So tags is a varchar field holding "1,4,6". Same logic applies, but now we have to split the field before reuniting it again. You cannot use something like "1 in tags" since "11" would return true ("1" is contained in "11" after all). (This is the SQL Antipattern known as "Jaywalking": see e.g. https://groups.google.com/forum/?fromgroups=#!topic/django-users/5j4AmQE6nTk )

SELECT pleft.name, GROUP_CONCAT(pright.tag)
FROM pleft JOIN pright
    ON ( CONCAT(',',pleft.tags,',') LIKE CONCAT('%,',pright.id,',%' ))
GROUP BY pleft.name ORDER BY pleft.name;

Another way would be to have a stored procedure: see http://www.marcogoncalves.com/2011/03/mysql-split-column-string-into-rows/ .

CREATE TABLE pleft ( id integer, name varchar(20), tags varchar(20) );
INSERT INTO pleft VALUES ( 1, 'Bob', '1,4,6' ), ( 2, 'Jill', '4,1' );
SELECT pleft.name, GROUP_CONCAT(pright.tag)
    FROM pleft JOIN pright
        ON ( CONCAT(',',pleft.tags,',') LIKE CONCAT('%,',pright.id,',%' ))
    GROUP BY pleft.name ORDER BY pleft.name;
+------+-----------------------------+
| name | GROUP_CONCAT(pright.tag)    |
+------+-----------------------------+
| Bob  | Sailing,Snowboarding,Skiing |
| Jill | Sailing,Snowboarding        |
+------+-----------------------------+

Wow thanks for the heads-up Isemi, I had no idea MySQL acted that way about the Jaywalkimg antipattern, that would have been some trouble later in the development, thanks! The workaround with the LIKE however will be slow for a table that will contain several hundred thousand rows. If there is no faster way I will create my third relational table — NaturalBornCamper, Sep 20 '12 at 19:14

score 1 · Answer 2 · answered Sep 20 '12 at 18:45

1

Try adding a GROUP BY the name field and you should be able to select a list of all names with their tags.

SELECT `name`, GROUP_CONCAT(`right`.`tag`) AS tags
FROM (`left`)
LEFT OUTER JOIN `right` ON `right`.`id` IN(left.tags)
GROUP BY `name`
ORDER BY `name` ASC;

answered Sep 20 '12 at 18:45

user1686928

11
1

1

Nah, not working, there's no point using a GROUP_BY anyway, the GROUP_CONCAT query returns only one row anyway. – NaturalBornCamper Sep 20 '12 at 18:59

Mysql JOIN with IN and multiple results of RIGHT table

2 Answers2