CODEWITHSUNDEEP
mysql
martincarlin87
martincarlin87

Reputation: 11042

MySQL Multiple Aliased Joins Against Joined Table

I have this query, which works fine:

select table_1.*, coalesce(test_1.type) as type
from `tbl_1`
left join `table_2` on `table_1`.`table_1_id` = `table_1`.`id`
inner join `table_3` as `test_1` on `test_1`.`code` = `table_2`.`column` and `table_2`.`column` = 'L'

So, it's a query on table 1 with a join on table 2, then subsequent joins from multiple aliased joins of table 3 on table 2, but as soon as I add further joins, I get no results and I'm not sure why, for example:

select table_1.*, coalesce(test_1.type, test_2.type) as type
from `tbl_1`
left join `table_2` on `table_1`.`table_1_id` = `table_1`.`id`
inner join `table_3` as `test_1` on `test_1`.`code` = `table_2`.`column` and `table_2`.`column` = 'L'
inner join `table_3` as `test_2` on `test_2`.`code` = `table_2`.`column` and `table_2`.`column` = 'H'

Can anyone explain what I have done wrong?

Upvotes: 0

Views: 31

Answers (2)

KHACHORNCHIT
KHACHORNCHIT

Reputation: 2320

What is the expected sample result of the second of your query ?
Could you please try this query ?

select table_1.*, coalesce(test_1.type) as type 
from `tbl_1` 
left join `table_2` on `table_1`.`table_1_id` = `table_1`.`id` 
inner join `table_3` as `test_1` on `test_1`.`code` = `table_2`.`column` and         
   (`table_2`.`column` = 'L' or `table_2`.`column` = 'H')

Upvotes: 1

SS_DBA
SS_DBA

Reputation: 2423

Try LEFT join on table_3 . If there are no records for table_3, that's why you yield no results, due to the INNER join.

And actually, you're not joining any columns on table_3. Is most likely the issue.

Upvotes: 1

Related Questions