getting related parent records only

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

getting related parent records only

Author

Topic

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2006-06-30 : 00:51:37

Hi friends
i've 3 tables.
provider :- parent
roles : child of provider
data : child of provider

the table "data" has 2 fields (say fld1 and fld2) that link back to "provider" table.
now how can i write a query that returns records from "provider" but only those that exist either in "roles" or "data" tables ?

Cheers

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-30 : 01:02:53

is this what you want ?

select *
from   provider p
where  exists (select * from roles r where r.fld1 = p.fld1)
or     exists (select * from data  d where d.fld1 = p.fld1)

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2006-06-30 : 01:09:09

almost there "KH"
what if i also want some fields from these child tables into main query ?

Cheers

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-30 : 01:17:14

something like this ?

select	p.fld1, r.fld2, d.fld3
from	provider p left join roles r
	on	p.fld1	= r.fld1
	left join data d
	on	p.fld1  = d.fld1
where	r.fld1 is not null
or	d.fld1 is not null

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2006-06-30 : 01:31:42

beautiful that is what i want.
Thank you very much KH.
you've been very helpful

Cheers

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources