SQL Server Performance Forum – Threads Archive
Qry–Need Help
Hello I have 2 Tables MasterTable Mastercode Mastername MasterKey masterField110 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
GO
SELECT DISTINCT city
FROM authors
WHERE NOT EXISTS
(SELECT 1
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
WHERE city NOT IN
(SELECT city
FROM publishers)
—
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
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.
]]>