Joining Parent / Child(ren) Tables

[ttyre]

Need to join the following parent and 2 child tables:

Parent
ParentID
Date
Child1
ParentID
Child1ID
Qty1
Child2
ParentID
Child2ID
Qty2

When I run a query:

Select Parent.Date, Parent.ParentID, Child1.Qty1, Child2.Qty2
From Parent LEFT JOIN Child1 ON Parent.ParentID = Child1.ParentID LEFT JOIN Child2 ON Parent.ParentID = Child2.ParentID

I am getting a Cartesian product (if a number of rows in Child1 table is different from number of rows in Child2 table for a given Parent.ParentID). What I would like to get are nulls instead of non-distinct values e.g.:

Date ParentID Qty1 Qty2
09/12/02 1 100 200
09/12/02 1 150 250
09/12/02 1 130 null

Will appreciate help/suggestions.

Ttyre

 

[osjohnm]

Hi

try an outer join...
Select Parent.Date, Parent.ParentID, Child1.Qty1, Child2.Qty2
From Parent LEFT OUTER JOIN Child1 ON Parent.ParentID = Child1.ParentID LEFT JOIN Child2 ON Parent.ParentID = Child2.ParentID

or perhaps like this

Select Parent.Date, Parent.ParentID, Child1.Qty1, Child2.Qty2
From Parent LEFT OUTER JOIN Child1 ON Parent.ParentID = Child1.ParentID LEFT OUTER JOIN Child2 ON Parent.ParentID = Child2.ParentID


I still use the old-style sql so my ansi sql isn't up to scratch but i think that the syntax is correct.

The outer joins will return data that matches the criteria and the null records

John

 

[ttyre]

John,

I tried outer join... without luck... I am still getting Cartesian product if a number of rows in Child1 table is different from number of rows in Child2 table for a given ParentID.

Ttyre

 

[rt63]

As far as I can figure out, your problem cannot be solved using a simple query with outer joins. The problem is that you want to do an outer join with your primary table with 2 secondary tables and as the query processing goes it has to result in a cartesian product because it will process 1 outer 2 and 1 outer 3 and a cartesian of the the two as we have not established any relation between the 2 child tables.

Guessing your requirements, let us say if your child table 1 has pid = 1 and cid1 = a and b and child table 2 has pid = 1 and cid2 = c , d and e then you probably require the output as

1st row : pid = 1, cid1 = a , qty1 , cid2 = c , qty2
2nd row : pid = 1, cid1 = b, qty1 , cid2 = d , qty2
3rd row : pid = 1 , cid1 = null , null , cid2 = e, qty2

if this is what u require, then u can achieve it using the following code


select * , 00000 as sqnc
into #temp_c1
from child1

update #temp_c1
set sqnc = (select count(*) from child1
where child1.parentid = #temp_c1.parentid
and child1.child1id <= #temp_c1.child1id)

select * , 00000 as sqnc
into #temp_c2
from child2

update #temp_c2
set sqnc = (select count(*) from child2
where child2.parentid = #temp_c2.parentid
and child2.child2id <= #temp_c2.child2id)

select a.parentid ,a.dt ,b.sqnc ,b.child1id ,b.qty1 ,c.child2id ,c.qty2
from parent a, #temp_c1 b , #temp_c2 c
where a.parentid = b.parentid
and a.parentid = c.parentid
and b.sqnc = c.sqnc
union
select a.parentid,a.dt,b.sqnc,b.child1id,b.qty1,null,null
from parent a, #temp_c1 b
where a.parentid = b.parentid
and b.sqnc > (select isnull(max(sqnc),0) from #temp_c2 where b.parentid = #temp_c2.parentid)
union
select a.parentid,a.dt,b.sqnc,null,null,b.child2id,b.qty2
from parent a, #temp_c2 b
where a.parentid = b.parentid
and b.sqnc > (select isnull(max(sqnc),0) from #temp_c1 where b.parentid = #temp_c1.parentid)
order by 1,3


I have added extra fields just to show the complete result set. You will need to alter as per ur requirements.


RT

 

[ttyre]

RT,

Great solution! I applied my fields to your sql code and it works like a charm....

Thanks a lot!

ttyre