Correlated joins

Question

Correlated joins

Denby

Hall of Fame

Points: 3120
More actions
May 30, 2007 at 9:48 am

#120341

Hi guys,
How can i achieve a correlated join in the following, but it aint working man.

From
dbo
.Check_Maggie
left
outer join
dbo
.Parents_Of_Child
on
dbo
.Check_Maggie.KPAIDS_NO = dbo.Parents_Of_Child.Child_KPAIDS_NO
left
outer join
dbo
.Patient_Pregnancy_Outcome
on
dbo
.Parents_Of_Child.Mother_KPAIDS_NO = dbo.Patient_Pregnancy_Outcome.KPAIDS_NO
inner
join
dbo
.Den_delivery_Mode_Lookup
on
dbo
.Patient_Pregnancy_Outcome.Mode_of_Delivery = dbo.Den_delivery_Mode_Lookup.Deliver_Id
(
(
dbo
.[Pregnancy Outcome Only] as POO
left
outer join
dbo
.Patient_Pregnancy_Outcome as PPO
on
POO
.Date_Delivered = PPO.Date_Delivered
)

Left
outer join
dbo
.Parents_Of_Child as PC
on
PC
.Child_KPAIDS_NO = PPO.Child_KPAIDS_NO
)

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

David McFarland SSChampion Points: 11815 More actions · Answer 1

The derived table below doesn't have a join to the other tables, as far as I can see, although some DDL would help if that isn't your issue. I'm assuming you are getting a syntax error, as I don't think the parser has any idea what you are wanting to do from the first open parenthesis on.

(

dbo

.[Pregnancy Outcome Only] as POO

left

outer join

dbo

.Patient_Pregnancy_Outcome as PPO

on

POO

.Date_Delivered = PPO.Date_Delivered

)

Left

outer join

dbo

.Parents_Of_Child as PC

on

PC

.Child_KPAIDS_NO = PPO.Child_KPAIDS_NO

)

Denby Hall of Fame Points: 3120 More actions · Answer 2

ok then, so how do i create a correlated join?

David McFarland SSChampion Points: 11815 More actions · Answer 3

It would help if you tell us what you are trying to accomplish, ideally with DDL for test data creation, your current code, and how you expect the output to look based on your test data.

-=JLK=- SSCertifiable Points: 5315 More actions · Answer 4

What do you mean by "Coorelated Join"? Granted I don't pay particular attention to all the "jargon" but I've heard of "Coorelated Sub-queries" which as I understand it means that it is "Executed once for each row of the out-query". I'm not sure what a "Coorelated Join" would be, since by definition a "join" is a set based operation and not a row based operation. Please explain (or if I'm way of the mark someone explain that also )

James.

Denby Hall of Fame Points: 3120 More actions · Answer 5

what i basically want to do, is to create a sub-join so to speak.

I have multiple join in the from clause, but there's a table already joined i want to rejoin to a prev join..

hope fully someone here has done it before, or someone to shed some more light on my quest

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

I think you need to post what you have, the DDL for the tables, some sample data, and what the results of the query should return. with that, there are many talented people out here that can assist.

David McFarland SSChampion Points: 11815 More actions · Answer 7

Sounds like a self-join. You can either look those up, or post more details so that we can help.