showing null rows using join
- by Pradyut Bhattacharya
Hi,
In mysql i m selecting from a table shouts
having a foreign key to another table named "roleuser"
with the matching column as user_id
Now the user_id column in the shouts table for some rows is null
(not actually null but with no inserts in mysql)
How to show all the rows of the shouts table either with user_id null or not
I m executing the sql statement
SELECT s.*, r.firstname, r.lastname
FROM shouts s left join roleuser r where r.user_id = s.user_id limit 50;
which does not executes and shows
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where r.user_id = s.user_id limit 50' at line 2
but using inner join the sql executes which shows rows
which only have user_id values in the shouts table. the nulls are not shown.
SELECT s.*, r.firstname, r.lastname
FROM shouts s inner join roleuser r where r.user_id = s.user_id limit 50;
How can i show all the rows from the shouts table and null values in the
firstname and lastname columns where the user_id is null in the shouts table.
If not at all possible with sql may be using stored procedures...
Thanks
Pradyut
