I have 3 tables: table with objects (A), table with properties (B) and a table with links object/property (A.id, B.id). The problem is that when I need to edit the object, I need to load data into the form, including the checkboxes state. As one of the possible solutions I see is to select data from the table with properties and from the table with objects and then write down the checkboxes using the nested loops, where the main loop will generate checkboxes and the subloop will be looking through the array of selections and will check the checkbox on id match, but I think there must be some way to select the data from the tables.
An example of data:
A.id B.id C(A.id B.id)
1 1 1 1
2 2 1 2
3 1 3
4 1 4
5
What I want to get is:
B.id A.id
1 1
2 1
3 1
4 1
5 NULL
So the B items with indexes 1,2,3,4 will be checked and 5 is unchecked. I've got this by using
SELECT DISTINCT B.id, A.id FROM B LEFT JOIN C ON B.id=C.id
WHERE A.id=<object id to edit> OR A.id IS NULL GROUP BY B.id
And it actually worked, but only for A.id=1. And with A.id=2 i've got
B.id A.id
5 NULL
Which means for me show only one unselected checkbox for property with id 5. Instead of something like:
B.id A.id
1 NULL
2 NULL
3 NULL
4 NULL
5 NULL
With A.id=2. Is there any way to achieve this or maybe I should use different logic?
Instead of using WHERE clause, you can specify needed A.id in joining condition.
SELECT DISTINCT B.id, C.a_id FROM B LEFT JOIN C ON B.id=C.b_id AND C.a_id=2 GROUP BY B.id
Try this out here: http://sqlfiddle.com/#!2/68efa/13/0
You can use a CASE without using the WHERE clause
SELECT tableb.`id`,
(CASE WHEN tablec.`aid`=2 THEN tablec.`aid` ELSE NULL END ) AS test
FROM tableb
LEFT JOIN tablec ON (tableb.`id` = tablec.`bid`)
It will work and gives you result in all conditions that you have the matched ids or not