sql - Joining 3 tables with unequal number of records

Question

select *
from servrectrans
where servrectrans.refwo ='W3298301'

Query returns 2 records

select *
from wpservice
where wpservice.wonum ='W3298301'

Query returns only one record

Now when I run the below query, I am getting 2 rows returned

SELECT 
    wpservice.plusplineprice as PlanServicePrice,
    wpservice.linecost as PlanServiceCost,
    servrectrans.plusplineprice as ActualServicePrice,
    servrectrans.linecost as ActualServiceCost
FROM
((workorder     
inner join wpservice WITH (NOLOCK) on  wpservice.wonum = workorder.wonum  )
inner join servrectrans WITH (NOLOCK)  on servrectrans.refwo = workorder.wonum)    
where WORKORDER.WONUM = 'W3298301';

I think the second entries for PlanServicePrice and PLanServiceCost are incorrect? They should NULL. Could this be fixed using a different type of join?

Thanks in advance.

Answer 1

That's how standard joins work. They match rows from different tables according to the join condition you specify. The engine is doing it right.

Now, to get the result you want you can:

1. Run queries separately and join in your app

You can query both tables separately and then just join the result sets in your app. Not that complicated but your app will need to account for different numbers of rows from each side.

2. Perform the join in the database

You don't mention the database so I'll assume it's a modern one that supports FULL OUTER JOINs (Oracle, PostgreSQL, DB2, SQL Server, etc.). If it doesn't, you'll need to tweak the query to simulate the full outer join:

You can do:

with
o as (
  select * from workorder where wonum = 'W3298301'
)
select 
  s.plusplineprice as PlanServicePrice,
  s.linecost as PlanServiceCost, 
  t.plusplineprice as ActualServicePrice, 
  t.linecost as ActualServiceCost 
from ( 
  select s.*, row_number() over(order by s.plusplineprice) as rn 
  from o join wpservice s on s.wonum = o.wonum 
) s 
full join ( 
  select t.*, row_number() over(order by t.plusplineprice) as rn 
  from o join servrectrans t on t.refwo = o.wonum 
) t on t.rn = s.rn