Query needed to retrive all the rows from one table joined with other table

Question

I have two tables, message, and user. I want a query that retrieves all the rows from the massage table, irrespective of the value of the user_id field NULL.

message table:

id,      msg_date,       sms_text,  contact_id, user_id
'1',     '2019-04-09 ',  'Hello 1',    '1',       '1'
'2',     '2019-04-10',   'Hello 2',    '1',       NULL
'3',     '2019-04-11',   'Hello 3',    '1',       '1'

user table:

# user_id, fullname
'1',       'Manish Sijaria'

if i execute the below query. it does not return row 2 from message table.


SELECT id, message_date , msg_from, msg_to, sms_text, contact_id, 
       message.user_id, user.fullname  
FROM message left join user on message.user_id=user.user_id 
                               and message.contact_id=1;

Result of above query:-

id,   message_date,   sms_text, contact_id, user_id, fullname
'1',  '2019-04-09',   'Hello 1',  '1',       '1',    'Manish Sijaria'
'3',  '2019-04-11 ',  'Hello 3',  '1',       '1',    'Manish Sijaria'

Row 2 is missing as user_id is NULL.

But I want the rows with user_id=NULL from message table as well. I want the below result:-

id,   message_date,   sms_text, contact_id, user_id, fullname
'1',  '2019-04-09',   'Hello 1',  '1',       '1',     'Manish Sijaria'
'2',  '2019-04-10 ',  'Hello 2',  '1',       NULL,     NULL
'3',  '2019-04-11 ',  'Hello 3',  '1',       '1',     'Manish Sijaria'

Kindly Suggest the query in Mysql.

Your query looks fine and is returning expected result for me. — Poorvi Nigotiya, Apr 12 '19 at 12:43

score 1 · Accepted Answer · answered Apr 12 '19 at 12:25

1

you can perform full outer join to get all the values please check this answer

so your query should be

SELECT id, message_date , msg_from, msg_to, sms_text, contact_id, 
       message.user_id, user.fullname  
FROM message left join user on message.user_id=user.user_id 
                               and message.contact_id=1
UNION ALL
 SELECT id, message_date , msg_from, msg_to, sms_text, contact_id, 
       message.user_id, user.fullname  
FROM message right join user on message.user_id=user.user_id 
                               and message.contact_id=1 and message.user_id is null

please also take a look at this pic

answered Apr 12 '19 at 12:25

Moneer Kamal

1,837
16
25

We do not need the second part of the query, first part itself will return the expected result – Poorvi Nigotiya Apr 12 '19 at 12:47
the first part will not return the values of message.user_id is null – Moneer Kamal Apr 12 '19 at 12:53
yes, the union query returned the expected result. Thanks Moneer ji. – user3903517 Apr 12 '19 at 14:43

Query needed to retrive all the rows from one table joined with other table

1 Answers1