CODEWITHSUNDEEP
sqldatabasejoin
Rain
Rain

Reputation: 259

SQL query joining four tables

Original query is joining customer table and contract table and Extra Service History, this all works.

However I'm having trouble adding 4th table which should apply some further criteria.

Current working query (no changes needed) :

select b.*
from SubscribersFIN b

inner join (select Id, Account_Number, ContractNumber, BackendId
from Contract) e on b.c_id='FI_' + e.Account_Number

left join (select Contract
from Extra_Service_History
where Service_Name='debit_plan') d on e.Id=d.Contract

where COUNTRY='fi' 
and NO_SMS = 0
and d.Contract is null

Goal is to filter the set that came from the big query that only records that had Paid status in Invoice to show. 

right join (select Contract 
from Invoice
where Status = 'PAID') i on e.Id=i.Contract

This one does not seem to do the trick, so I'm not able to figure out what sort of a join-type or logic is required here.

Upvotes: 1

Views: 234

Answers (3)

Kiran Kommalapudi
Kiran Kommalapudi

Reputation: 11

Based on my understanding i just re arranged the query. Try this. If your where condition columns are coming from any of the LEFT JOIN tables, join them at the on clause.

select b.* from SubscribersFIN b
inner join Contract e  on b.c_id='FI_' + e.Account_Number
left join  Extra_Service_History d on e.Id=d.Contract and d.Service_Name='debit_plan' and d.Contract is null
left join invoice i on e.Id=i.Contract and i.Status = 'PAID'
where COUNTRY='fi'  and NO_SMS = 0

Upvotes: 1

Disillusioned
Disillusioned

Reputation: 14832

You have a few options:

  1. INNER JOIN

Depending on the particular type of outer join, they return rows where no match is found (either left, right, or both sides of the join). Based on your description this is not what you want. Simply use:

inner join (select Contract 
from Invoice
where Status = 'PAID') i on e.Id=i.Contract

It shouldn't matter where this occurs in the FROM clause; provided the join between these 2 tables is INNER. The query engine is free to rearrange for performance provided it doesn't change semantics. (But personally I find it tidier to put INNER JOINs at the top.)

  1. IN filter

What you've described is a filter.

Goal is to filter the set that came from the big query that only records that had Paid status in Invoice to show.

So it's clearer to implement this as a filter in the WHERE clause. E.g.

where e.Id in (select  Contract 
               from    Invoice
               where   Status = 'PAID')
  and ...
  1. EXISTS filter

Similar to the above, but using an EXISTS subquery instead.

where exists (select  *
              from    Invoice i
              where   Status = 'PAID'
                  and i.Contract = e.Id)
  and ...

Upvotes: 1

Greg Viers
Greg Viers

Reputation: 3523

Rather than mixing LEFT and RIGHT joins, just place it as an INNER join higher up in your query:

select b.*
from SubscribersFIN b

inner join (select Id, Account_Number, ContractNumber, BackendId
from Contract) e on b.c_id='FI_' + e.Account_Number

inner join (select Contract 
from Invoice
where Status = 'PAID') i on e.Id=i.Contract

left join (select Contract
from Extra_Service_History
where Service_Name='debit_plan') d on e.Id=d.Contract

where COUNTRY='fi' 
and NO_SMS = 0
and d.Contract is null

Upvotes: 1

Related Questions