Not Exists

[mikev1976]

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

[justsomeguy]

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.

[mikev1976]

I tried that as well and started to question if I needed that or not. In order to use NOT EXISTS, does more than one table need to be involved?

[justsomeguy]

No, but EXISTS is different than a join.

[mikev1976]

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

[justsomeguy]

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

[mikev1976]

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...

[justsomeguy]

That's virtually the same thing, I just used an alias for the function result.

[mikev1976]

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.

[mikev1976]

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)

[mikev1976]

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

[justsomeguy]

No, there's no join there.

[Synook]

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