Qry–Need Help | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Qry–Need Help

Hello I have 2 Tables MasterTable Mastercode Mastername MasterKey masterField1
10 asldjfa 1 asdlfasd Childtable
ChildCode MasterCode MasterKey
1 10 5
2 10 1
3 10 7 MasterKey is a variable field in master table where it is assigned with different values based on situations. Now what i have to do is check if MasterKey Exists in ChildTable and if yes then select that masterrecord and if not ignore that mastertable record. ie I want only those masterrecords whose masterkey value is in Childtable for that Mastercode. Thanks for the help.

Select M.* from Master M
inner join Child C on M.MasterCode =C.MasterCode and M.MasterKey =C.MasterKey Madhivanan Failing to plan is Planning to fail
Hello Madhivanan Thanks for the reply.
I just made a mistake in my request.Actually what i want is the opposite.
I want to have the mastertable records for those which do not have masterkey in childmaster. Select M.* from Master M
inner join Child C on M.MasterCode =C.MasterCode and M.MasterKey <>C.MasterKey I tried this method but still it fetches the master record for there is a M.master and C.Maseter relationship is valied
and there are records which dont have MasterKey. If masterkey exists in the child table then i dont want that record. If master keydont exists then i want the record. Thanks

Not entirely sure if that is what you want, but try this
USE pubs
FROM authors
FROM publishers
WHERE authors.city = publishers.city) SELECT DISTINCT authors.city
FROM authors
LEFT JOIN publishers
ON authors.city = publishers.city
WHERE publishers.city IS NULL SELECT DISTINCT city
FROM authors
(SELECT city
FROM publishers)

Frank Kalis
Microsoft SQL Server MVP
Heute schon gebloggt?http://www.insidesql.de/blogs
Ich unterstuetze PASS Deutschland e.V. http://www.sqlpass.de)
2 soulutions you can use:
1. where not exists(…).
2. Use outer join and where child.pk is null.

Thanks mmarovic,FrankKalis and Madhivanan It worked.Great help.