Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

sql server query

Posted on 2016-09-08
7
Medium Priority
?
44 Views
Last Modified: 2016-09-08
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

0
Comment
Question by:PeterBaileyUk
  • 3
  • 3
7 Comments
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 41789279
What output are you wanting to get?
0
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 41789281
I'm not sure on what you pretend to do. Can you post some sample data for better understanding?
0
 

Author Comment

by:PeterBaileyUk
ID: 41789287
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
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 

Author Comment

by:PeterBaileyUk
ID: 41789289
L5EMW_T_6' was just an example. i am looking for orphaned codes as the tblword will get updated from time to time.
0
 
LVL 52

Accepted Solution

by:
Vitor Montalvão earned 2000 total points
ID: 41789293
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
 

Author Closing Comment

by:PeterBaileyUk
ID: 41789297
i had made it more complicated and wrong too..thank you
0
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 41789303
KIS = Keep It Simple :)

Sometimes is better to drop all and start again taking another root.
Cheers.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

876 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question