Alko Alko - 4 months ago 8
SQL Question

MySql, join tables WHERE IN()

I have two tables and structure here:

CREATE TABLE `users` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`username` varchar(50) NOT NULL,
`membership` varchar(12) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;


CREATE TABLE `memberships` (
`id` int(1) unsigned NOT NULL AUTO_INCREMENT,
`title` varchar(30) NOT NULL DEFAULT '',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;



INSERT INTO `memberships` (`id`, `title`) VALUES
(1, 'Basic'),
(2, 'Bronze'),
(3, 'Gold'),
(4, 'Platinum');

INSERT INTO `users` (`id`, `username`, `membership`) VALUES
(1, 'john', '0'),
(2, 'mike', '1,2'),
(3, 'peter', '3,4'),
(4, 'jane', '3'),
(5, 'rick', '1,2,3');


I need to join both tables and list all users with their memberships using IN function.

This is what I tried, but I'm not able to join tables that way:

SELECT
*.u
m.title AS title
FROM
`users` AS u
LEFT JOIN `memberships` AS m
WHERE m.id IN(u.membership)
ORDER BY u.username;


What I would like to have is something like this:

username membership
=====================================
john null
mike Basic, Bronze
peter Gold, Platinum
jane Gold
rick Basic, Bronze, Gold


I have created SQL fiddle as well: http://sqlfiddle.com/#!9/39b6e1

Answer

How about this?

SELECT 
  u.*,
  GROUP_CONCAT(m.title SEPARATOR ', ') as memberships
FROM
  `users` AS u 
  LEFT JOIN `memberships` AS m 
    ON FIND_IN_SET(m.id, u.membership)
GROUP BY u.username
ORDER BY u.username 

Gives you:

id  username    membership  memberships
4   jane        3           Gold
1   john        0           (null)
2   mike        1,2         Bronze, Basic
3   peter       3,4         Gold, Platinum
5   rick        1,2,3       Basic, Bronze, Gold

Updated SQL Fiddle

Comments