Problem with query. Looking for an elegant method to solve it

I have three tables in the following form.

开发者_JAVA百科

C  
1
2
3
4

B
1
2
3
4
5


 A B C 
 1 1 1
 2 1 2
 3 1 3
 4 1 4
 5 2 1
 6 2 2
 7 3 1
 8 4 1
 9 5 1

And want to show list with all "B" who do not have corresponding "C". For example:

 -----------
 |2|3,4    |
 |3|2,3,4,5|
 |4|2,3,4,5|
 |5|2,3,4,5|
 -----------

I do not have much experience with sql. I'll be glad of any assistance or prompting.

---

Untested but I think something like the following should work.

SELECT  b.B, 
        GROUP_CONCAT(c.C) AS C
FROM    tableB b
        CROSS JOIN tableC c
        LEFT JOIN tableABC abc ON abc.B = b.B
                                  AND c.C = abc.C
WHERE   abc.B IS NULL
GROUP BY b.B

Edit: Though bizarrely it appears as though MySQL has redefined CROSS JOIN to mean the same as INNER JOIN so you might need to achieve the CROSS JOIN by using tableB b, tableC c instead of tableB b CROSS JOIN tableC c

---

You will probably be looking for something along the lines of: (assuming the table numbers are fields called 'id'), and the A table is (id, b_id, c_id)

While more information would be helpful, this might give you some information to work with

    SELECT B.id, group_concat(A.id) 
    FROM A 
    LEFT JOIN B on A.b_id = B.id 
    LEFT JOIN C on A.c_id = C.id
    WHERE 
    A.c_id IS NULL
    GROUP BY B.id