sql server query

I am trying to create a query that will tell me if the clientcodewordposition is in tbltagwords but not in tblwords.

I thought this would do it:
but it doest

SELECT TW.ClientCodeWordPosition, sub.ClientCodeWordPosition
FROM TblWordTags  TW LEFT JOIN TblWordTags ON (TW.ClientCodeWordPosition = TblWordTags.ClientCodeWordPosition)

join ( select W.ClientCodeWordPosition
	FROM TblWords W
	where w.ClientCodeWordPosition is null
	GROUP BY W.ClientCodeWordPosition ) sub

	on TW.ClientCodeWordPosition=sub.ClientCodeWordPosition
	GROUP BY TW.ClientCodeWordPosition, sub.ClientCodeWordPosition

Open in new window

PeterBaileyUkAsked:
Who is Participating?
 
Vitor MontalvãoConnect With a Mentor MSSQL Senior EngineerCommented:
This gives you all non existence words:
SELECT ClientCodeWordPosition 
FROM TblWordTags  
WHERE NOT EXISTS (SELECT 1 
              FROM tblWords 
              WHERE tblWords.ClientCodeWordPosition=TblWordTags.ClientCodeWordPosition)

Open in new window

and this is the DELETE command that you'll need:
DELETE
FROM TblWordTags  
WHERE NOT EXISTS (SELECT 1 
              FROM tblWords 
              WHERE tblWords.ClientCodeWordPosition=TblWordTags.ClientCodeWordPosition)

Open in new window

1
 
Lee SavidgeCommented:
What output are you wanting to get?
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
I'm not sure on what you pretend to do. Can you post some sample data for better understanding?
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
PeterBaileyUkAuthor Commented:
if pk in TblWordTags  is 'L5EMW_T_6' (ClientCodeWordPosition is pk field) and does not appear in tblWords (ClientCodeWordPosition is pk field)  then give output so I can delete it from tblwordtags.
0
 
PeterBaileyUkAuthor Commented:
L5EMW_T_6' was just an example. i am looking for orphaned codes as the tblword will get updated from time to time.
0
 
PeterBaileyUkAuthor Commented:
i had made it more complicated and wrong too..thank you
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
KIS = Keep It Simple :)

Sometimes is better to drop all and start again taking another root.
Cheers.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.