I am trying to use left join using few conditions, i am getting results as inner join instead of lef-CodePudding

I want all records using left join from tab;le 1, Table 2 and Table 3 for April month and also where city = london. I am expecting 2 records in final result and with first record = london and second record null or blank.

    Table1                Table2                table3      
    id      amt month       id      ac      month       ac      city    month
    A123    400 April       A123    ac11    April       ac11    london  April
    A124    500 April       A124    ac12    April       ac12    milan   April
    A123    600 May         A123    ac11    May         ac11    london  May
    A124    700 May         A124    ac12    May         ac12    milan   May



Desired Result              
Table3              
id      amt ac      city    month
A123    400 ac11    london  April
A124    500 ac12            April



 Actual Result              
Table3              
id      amt ac      city    month
A123    400 ac11    london  April

I am using squirrel sql and not getting desired result using following query. Let me know how to correct it.

select a.id, a.amt, b.ac, c.city, a.month from table1 as a 
left join table2 as b on a.id=b.id 
left join table3 as c on b.ac=c.ac 
where a.month = 'April' and b.month = 'April'  c.month = 'April' 
and c.city = 'london'

CodePudding user response：

You can’t filter the tables being joined in a left join(in your case table b and c) inside the where clause. Every filter that need to be applied on these tables should be placed inside the ON clause, otherwise you join simple becomes an inner join. E.G.

left join table2 as b on (a.id = b.id and b.month = ‘April’)

CodePudding user response：

Easy way to use joins. I often look at this diagram.