Please forgive me in case a similar question has already been asked, but I was not able to find it. The problem with searching is that expressing the question is difficult in a few words, though the setup is quite simple:
Basically, I have entries that can be assigned to lists. For that purpose I have three tables:
mysql> SELECT * FROM list;
+-----+-----------+
| lid | listname |
+-----+-----------+
| 1 | Fine List |
| 2 | Bad List |
+-----+-----------+
2 rows in set (0.00 sec)
mysql> SELECT * FROM entry;
+-----+-----------+
| eid | entryname |
+-----+-----------+
| 1 | red |
| 2 | green |
| 3 | blue |
| 4 | gray |
| 5 | black |
+-----+-----------+
5 rows in set (0.00 sec)
mysql> SELECT * FROM entry2list;
+-----+-----+
| eid | lid |
+-----+-----+
| 1 | 1 |
| 2 | 1 |
| 5 | 1 |
| 2 | 2 |
| 4 | 2 |
+-----+-----+
5 rows in set (0.00 sec)
So as a result I have 2 lists, one with 2 colors assigned, one with 3.
I can easily get a list with entries on a certain list:
SELECT * FROM list
LEFT JOIN entry2list USING (lid)
LEFT JOIN entry USING (eid)
WHERE lid=1
But I need the missing entries also, which is much more dificult:
SELECT tmp.eid, e.* FROM entry e
LEFT JOIN (
SELECT * FROM list
LEFT JOIN entry2list USING (lid)
LEFT JOIN entry USING (eid)
WHERE lid=2) AS tmp
USING (eid)
+---------+-----+-----------+
| tmp.eid | eid | entryname |
+---------+-----+-----------+
| NULL | 1 | red |
| 2 | 2 | green |
| NULL | 3 | blue |
| 2 | 4 | gray |
| NULL | 5 | black |
+---------+-----+-----------+
In my case, I need a yes/no list to see if an entry is in the list or not:
SELECT GROUP_CONCAT(IF(tmp.eid IS NULL, 'n', 'y')) AS is_set FROM entry e
LEFT JOIN (
SELECT * FROM list
LEFT JOIN entry2list USING (lid)
LEFT JOIN entry USING (eid)
WHERE lid=2) AS tmp
USING (eid)
which results in
+-----------+
| is_set |
+-----------+
| n,y,n,y,n |
+-----------+
But now I am stuck; The result is basically what I need, but the last demand on the request is that I need a similar row for every list, so the lid in the where class has be substituted somehow (or, more probable, the request has to be converted completely).
What I would like to get as a result is something like
+-----+-----------+
| lid | is_set |
+-----+-----------+
| 1 | y,y,y,n,n |
| 2 | n,y,n,y,n |
+-----+-----------+
Is that possible? How can it be done?
First, generate the rows that you need -- which are all lists and all entries. Then bring in the information about what belongs to what, and aggregate to get the strange form of output that you want.
The following produces what you want, with a separate row for each list and entry:
select l.listname, e.entryname,
(case when el.lid is null then 'n' else 'y' end) as InList
from list l cross join
entry e left join
entry2list el
on l.lid = el.lid and e.eid = el.eid;
For the format that you want, you need a group by and group concat. Something like this:
select l.lid, l.listname,
group_concat(case when el.lid is null then 'n' else 'y' end order by e.entryname) as ListFlags
from list l cross join
entry e left join
entry2list el
on l.lid = el.lid and e.eid = el.eid
group by l.lid;