Jump to content
mikev1976

Not Exists

Recommended Posts

I am having trouble using the NOT EXISTS command. Here is the situation: I have a table with the following structure: [string] [Locale] [Value ]ABCD 1 YesABCD 2 SiEFGH 1 PregnancyEFGH 2 Embarazo Everything should have 2 values for one string...an English version and a Spanish version. I was working on something yesterday and found a was missing Spanish translation. So I inserted the value but it made me wonder how many other values are missing a Spanish translation? I inserted it using a sub query like so: if not exists(select * from table where Value='Spanish translation')insert tableselect(select String from table where Value ='English version'),2,'Spanish translation' I cannot get the logic right to find the ones that have an English version but no Spanish. It always displays 0 results when I try to use the NOT EXISTS. From all of the examples I find online, there are two tables involved and a join. I don't need to look in a second table. I have all of my info in one. I want to find all the rows that have a Locale 1 but not associated Locale 2. This is what I was trying: select *from tablewhere (exists(select * from table where LocaleId=1) and not exists(select * from table where LocaleId=2)) Thanks in advance for any help

Share this post


Link to post
Share on other sites

You can use the join and just join it on the same table, you don't need the tables you join to be different. The solution will have a join in it.

Share this post


Link to post
Share on other sites

I realize they are different. Using an EXIST means that the outer query only runs if the sub query returns a true value. A JOIN joins two tables together. I think I am half way there. It seems I can display the ones that only have both locale values. I want just the opposite...only display the ones that don't have both. This is how I am getting the ones that have both: select * from table as table1join table as Vtable1on table1.String = Vtable1.String where table1.Locale =1 and Vtable1.LocaleId=2

Share this post


Link to post
Share on other sites

I didn't think about it before, but it's easier to use group by:

SELECT string, COUNT(string) AS num FROM table GROUP BY string HAVING num = 1

Share this post


Link to post
Share on other sites

When using the HAVING command, it has to be used in conjunction with a function. By using this method, I think I can see the total number of rows that do not have both values. Since each string should have 2 values (1 for English and 1 for Spanish) I did the following: select Stringfrom tablegroup by Stringhaving COUNT(String)=1 getting closer...

Share this post


Link to post
Share on other sites

O. I tried doing it that way at first and it told me that num was an invalid column name on the HAVING line. I can do it this way: select String,COUNT(String) as [how many]from tablegroup by Stringhaving COUNT(String) =1This displays two columns, the string and another column named how many with the count id in it(1). This displays some of what I want but not all. When I add the other columns I want, it gets messed up because of the group by I guess. I tried adding the additional columns there but it gets it out of whack.

Edited by mikev1976

Share this post


Link to post
Share on other sites

Ok...I thinI have it.... select *from table as table1where not exists(select * from table as Vtable1 where Vtable1.String =table1.String and Vtable1.Locale=2)

Share this post


Link to post
Share on other sites

This is the code I'm using and it works fine. I understand that if the subquery returns no rows then the outer query will be run. My question is if a join is being created between Member.Users and Security.EntityReferences to be able to do the subquery. insert @missingusersselect u.UserId,@sponsorid,NEWID()from Member.Users u where not exists(select * from Security.EntityReferences er where er.EntitySecurityKey=u.UserId )and SponsorId=@sponsorid

Share this post


Link to post
Share on other sites

It's a correlated subquery so the complexity is similar, if that is what you are wondering.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×