CODEWITHSUNDEEP
sqlsql-serverjoinleft-join
Vishal Suthar
Vishal Suthar

Reputation: 17193

Left Join gives no result

These are the two tables.

tbl_Invoice

Invoice

tbl_Payment

Payment

What I want is Pending Invoices by a particular Client.

Existing Query is as below:

Select * from tbl_Invoice I 
    left join tbl_payment P on I.client_id = P.client_id
    left join tbl_client C on I.client_id = C.client_id
    where I.invoice_Id not in (P.invoice_Id) 
    and I.client_id = 8

But it gives blank result becuase the Payment table is blank.

Upvotes: 3

Views: 1681

Answers (1)

a1ex07
a1ex07

Reputation: 37354

You need to move condition from WHERE to ON :

Select * from tbl_Invoice I 
left join tbl_payment P on (I.client_id = P.client_id 
 AND p.invoice_id <> I.invoice_Id)
left join tbl_client C on I.client_id = C.client_id

WHERE I.client_id = 5

Upvotes: 9

Related Questions