CODEWITHSUNDEEP
mysqlsql
monkey coder
monkey coder

Reputation: 163

2 left joins with a union

tbl_chat:

message_id  message_text    users_id  another_user   time_sent
1           'hi'                9        1           2014-10-13 00:10:32   
2           'hello'             1        9           2014-10-13 00:12:32
3           'good morning'      9        1           2014-10-13 00:12:34
4           'good night'        9        1           2014-10-13 00:14:02
5           'LOL'               1        9           2014-10-13 00:14:05

tbl_usersinfo:

users_id    users_fname     users_lname
1            ben               ten
9            son               goku

I want to get all the conversation of these people, and show their full name, message, and the time that they sent that message, but my query doesn't return the name of them correctly, here is my query:

SELECT CONCAT_WS(' ',i.users_fname, i.users_lname) AS full_name, c.message_text,c.time_sent,c.message_id
FROM tbl_chat AS c
LEFT JOIN tbl_usersinfo AS i ON i.users_id = c.another_user
WHERE c.users_id = 1
UNION
SELECT CONCAT_WS(' ',i.users_fname, i.users_lname) AS full_name, c.message_text,c.time_sent,c.message_id
FROM tbl_chat AS c
LEFT JOIN tbl_usersinfo AS i ON i.users_id = c.users_id
WHERE c.users_id = 9
ORDER BY time_sent ASC

the result of this query will be:

full_name  message_text        time_sent             message_id
 son goku    'hi'               2014-10-13 00:10:32     1
 son goku    'hello'            2014-10-13 00:12:32     2 
 son goku    'good morning'     2014-10-13 00:12:34     3
 son goku    'good night'       2014-10-13 00:14:02     4
 son goku    'lol'              2014-10-13 00:14:05     5

but my desired output is:

full_name  message_text        time_sent             message_id
son goku    'hi'               2014-10-13 00:10:32    1
ben ten     'hello'            2014-10-13 00:12:32    2 
son goku    'good morning'     2014-10-13 00:12:34    3
son goku    'good night'       2014-10-13 00:14:02    4
ben ten     'lol'              2014-10-13 00:14:05    5

Upvotes: 0

Views: 54

Answers (3)

Paladin
Paladin

Reputation: 630

Well, i have not used any union or left join assuming that userId of tbl_chat will always be present in the chatinfo table. I am getting the correct output using the following,

select concat(users_fname,' ', users_lname), message_text, time_sent , message_id
from tbl_chat t
join tbl_usersinfo u
on u.users_id = t.users_id
order by message_id

Based on the scenario listed in the question, this works and this will be fast because its not using any union.

Upvotes: 0

Ram
Ram

Reputation: 3091

There is a minor logic error

SELECT CONCAT_WS(' ',i.users_fname, i.users_lname) AS full_name, c.message_text,c.time_sent,c.message_id
FROM tbl_chat AS c
LEFT JOIN tbl_usersinfo AS i ON i.users_id = c.another_user // this is another user so turning 1 to 9 
WHERE c.users_id = 1
UNION
SELECT CONCAT_WS(' ',i.users_fname, i.users_lname) AS full_name, c.message_text,c.time_sent,c.message_id
FROM tbl_chat AS c
LEFT JOIN tbl_usersinfo AS i ON i.users_id = c.users_id 
WHERE c.users_id = 9
ORDER BY time_sent ASC

To correct this you can change i.users_id = c.another_user to i.users_id = c.users_id but the best thing is remove the union and keep the query simple like @Rimas pointed out

SELECT CONCAT_WS(' ',i.users_fname, i.users_lname) AS full_name, c.message_text,c.time_sent,c.message_id
FROM tbl_chat AS c
LEFT JOIN tbl_usersinfo AS i ON i.users_id = c.users_id 
WHERE c.users_id in (1,9) AND c.another_user in (1,9)

Upvotes: 1

Rimas
Rimas

Reputation: 6024

SELECT CONCAT_WS(' ',i.users_fname, i.users_lname) AS full_name, c.message_text,c.time_sent,c.message_id
FROM tbl_chat AS c
LEFT JOIN tbl_usersinfo AS i ON i.users_id = c.users_id
WHERE (c.users_id = 1 AND c.another_user = 9)
   OR (c.users_id = 9 AND c.another_user = 1)
ORDER BY time_sent ASC

Upvotes: 3

Related Questions