I'm using two tables in the database and query The tables look like:
Table student
id | studentid | name | room
----------------------------------------
1 | 28778 | a | 1
2 | 28779 | b | 2
3 | 28785 | c | 2
4 | 28300 | d | 2
5 | 28301 | e | 2
6 | 28302 | f | 2
7 | 28303 | g | 2
8 | 28304 | h | 3
9 | 28305 | i | 3
10 | 28306 | j | 3
Table image
id |student_id| image_filename | type |
---------------------------------------------------
1 | 1 | qwrioqw.jpg | m6-1 |
2 | 1 | oerqew.jpg | m6-2 |
3 | 2 | qwwqeqw.jpg | m6-2 |
4 | 4 | wqeioqw.jpg | m6-1 |
5 | 4 | qwwoqeqw.jpg | m6-2 |
6 | 7 | eqwrioqw.jpg | m6-1 |
7 | 7 | rewtoqw.jpg | m6-2 |
8 | 8 | asdsadas.jpg | m6-2 |
I used command SELECT name, id, image.image_filename, image.type FROM image RIGHT JOIN student ON student_id=student.id WHERE room=2 HAVING image.type = 'm6-2' ORDER BY id
The result in this command is:
name | id | image_filename | type |
---------------------------------------------------
b | 2 | qwwqeqw.jpg | m6-2 |
d | 4 | qwwoqeqw.jpg | m6-2 |
g | 7 | rewtoqw.jpg | m6-2 |
BUT I want result All student.room = 2 AND image.type = 'm6-2' if image m6-2 is none i want show NULL :
name | id | image_filename | type
-----------------------------------------------
b | 2 | qwwqeqw.jpg | m6-2
c | 3 | NULL | NULL
d | 4 | rewtoqw.jpg | m6-2
e | 5 | NULL | NULL
f | 6 | NULL | NULL
g | 7 | rewtoqw.jpg | m6-2
How do I write a command ?
You can try below - using put your others condition on ON Clause instead of Where Clause
SELECT name, id, image.image_filename, image.type
FROM image RIGHT JOIN student ON student_id=student.id
and room=2 and image.type = 'm6-2'