Joining issue with mysql

Question

I have 2 tables and I am trying to join them using left join

Table 1

hid    rid   uid    tie

1      61    566     23
2      62    322     21
3      63    455     65
4      63    223     29

Table 2

My desired output should be

uid     tie
322     21
455     65
223     29
344     0

My query is

select table1.uid,table1.tie from  table1 left join table2 on
table2.uhid = table1.uid   
where (table1.rid=61 or table1.rid=62 or table1.rid=63)

But it gives me the following result which is not desired.

uid     tie
566     23
322     21
455     65
223     29
344     0

I don't want 566 not to be included as it is not included in table 2 though it's rid is 61 and it included in where clause.

Any help is highly appreciated. Thanks in advance.

Corrected

select table1.uid,table1.tie from  table2 left join table1 on
table2.uhid = table1.uid   
and (table1.rid=61 or table1.rid=62 or table1.rid=63)

Thank you, @MarcellFülöp . I couldn't wrap my head around what @Raj was trying to get at. @Rah, like Marcell said, either `Table2 LEFT JOIN Table1' or change your `Left Join` to a 'Right Join` — JNevill, Dec 16 '14 at 17:26
i dont understand how 344 is in your actual result, it doesnt join to anything nor is it in your left table...are you sure this information is accurate? — Logan Murphy, Dec 16 '14 at 17:28
its also amazing you got the 344 and the 0...there must be something you are not telling us...because i don't get nearly the same results with just as much effort...since 344 has no associated `rid` column thus making it null and not equal to 61, 62, 63 — Logan Murphy, Dec 16 '14 at 17:39
The working query is now in my question.table2 left join table 1 and removing where and bringing it to as "and" did the trick — Raj, Dec 16 '14 at 17:46
@Raj so you are saying you answered your own question? because i dont get the same result you claim you do for either of your queries but the one i wrote (the third) works http://sqlfiddle.com/#!2/7b6b67/9 ...is there some option or other fowl magic at work here? please enlighten me maybe leave a comment on my answer? — Logan Murphy, Dec 16 '14 at 17:52
@Raj after further testing your corrected query it turns out any row in the left table that does not pass the `on` condition actually passes null values to the result as is demonstrated here http://sqlfiddle.com/#!2/a548c/1 ...this is because EVERY row from the left table is guarentees passage to the result weither or not the `on` condition passes...`on` just determines if the columns from the other table have values or not — Logan Murphy, Dec 16 '14 at 18:09
@LoganMurphy My query is like this--select table2.uhid,table1.tie from table2 left join table1 on table2.uhid = table1.uid and (table1.rid=61 or table1.rid=62 or table1.rid=63); — Raj, Dec 16 '14 at 18:09

score 1 · Answer 1 · edited May 23 '17 at 10:29

1

A RIGHT JOIN is what you should be using. You might want to go through this link

edited May 23 '17 at 10:29

Community

1
1

answered Dec 16 '14 at 17:28

f1zz0_13

495
2
8
19

Logan Murphy · Accepted Answer · 2014-12-16T18:14:16.720

Using a right join is a good first step but it still won't get you the result you want.

select table2.uhid,ifnull(table1.tie, 0) from  table1 right join table2 on
table2.uhid = table1.uid   
where (table1.rid=61 or table1.rid=62 or table1.rid=63 or table1.rid is null);

Maybe you need some help understanding how left / right join works.

Left join will ensure that EVERY row from the table on the left side of the join is included regardless of the on clause (Similar for right). The on clause merely determines if the other table has column values or not. Only problem is when the on clause fails the other table's columns will have null values that need to be accounted for which this

ifnull(table1.tie, 0)

and this

or table1.rid is null

accounts for

Here is a sql fiddle demonstration

http://sqlfiddle.com/#!2/7b6b67/7

Hi Logan the query which I stated on comment section works but not optimised as yours. I would like to follow yours. — Raj, Dec 16 '14 at 18:10

Joining issue with mysql

2 Answers2