where clause in sql [modified]

[AndyInUK]

SELECT DISTINCT test
FROM [abc].[dbo].[a]
inner join [def].[dbo].[b]
on [abc].[dbo].[a].[mail] = [def].[dbo].[b].mail
inner join idx
on idx.uid = mail.uid
where id = 'GHREFC5C-GT5F-43RF-GRT4-TGREW34TGY67'
and id <> '4RT467YH-RF43-45FH-78YG-54RYG6THYR43'

The above query filters the first id but it is ignoring the and bit ? So

where id = 'GHREFC5C-GT5F-43RF-GRT4-TGREW34TGY67'

works but

and id <> '4RT467YH-RF43-45FH-78YG-54RYG6THYR43'

doesn't. Is there anything am doing wrong. Can i not put where clause like above. Thank You Andyyy

modified on Monday, December 14, 2009 6:28 AM

[David Skelly]

AndyInUK wrote:

where id = 'GHREFC5C-GT5F-43RF-GRT4-TGREW34TGY67' and id <> '4RT467YH-RF43-45FH-78YG-54RYG6THYR43'

What are you trying to achieve with this? If id = 'GHREFC5C-GT5F-43RF-GRT4-TGREW34TGY67' is true then logically id <> '4RT467YH-RF43-45FH-78YG-54RYG6THYR43' must also be true, so it is unnecessary. On the other hand, if id = 'GHREFC5C-GT5F-43RF-GRT4-TGREW34TGY67' is false then the second test after the AND is irrelevant and it doesn't matter whether it is true or false. I don't understand why you have included the test for id <> '4RT467YH-RF43-45FH-78YG-54RYG6THYR43' since it doesn't seem to do anything useful in your query, unless I am missing something.

[AndyInUK]

basically user can have many ids - So i have to get the users with id = 'GHREFC5C-GT5F-43RF-GRT4-TGREW34TGY67' only and it should not include the users who has got id = 'GHREFC5C-GT5F-43RF-GRT4-TGREW34TGY67' and also id = '4RT467YH-RF43-45FH-78YG-54RYG6THYR43' Becuase if i only use the first where clause it gives me users with that id plus they have many other ids associated with them which i don't want.

[Luc Pattyn]

SELECT car WHERE color='red' AND color<>'green' Does that seem logical to you? :)

Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]

---

I only read code that is properly indented, and rendered in a non-proportional font; hint: use PRE tags in forum messages

---

[AndyInUK]

Say Bob is opted in 4 ids - a,b, c, d. Now i want to find out users who are opted in a but not c. So how can i find that ?

[Lost User]

By using a subquery;

SELECT *
FROM [dbo].[A]
WHERE id = 'bla'
AND id NOT IN (
SELECT [id]
FROM [dbo].[C])

Can you show us the definition of tables A and C? How many records does a single user have in each table?

I are Troll :suss:

[Luc Pattyn]

something along these lines should work: SELECT * FROM `CPtest` WHERE optio = 1 AND NAME NOT IN (SELECT name FROM `CPtest` WHERE NOT optio = 1) :)

Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]

---

I only read code that is properly indented, and rendered in a non-proportional font; hint: use PRE tags in forum messages

---

[Niladri_Biswas]

Take this example

declare @t table(username varchar(50),userid varchar(100))
insert into @t
select 'username1','a' union all select 'username1','b' union all
select 'username1','c' union all select 'username1','d' union all
select 'username2','a' union all select 'username3','b' union all
select 'username3','c' union all select 'username3','d' union all
select 'username4','a' union all select 'username4','b' union all
select 'username4','c' union all select 'username5','d' union all
select 'username6','a' union all select 'username7','b' union all
select 'username8','c' union all select 'username8','d'

username userid
username1 a
username1 b
username1 c
username1 d
username2 a
username3 b
username3 c
username3 d
username4 a
username4 b
username4 c
username5 d
username6 a
username7 b
username8 c
username8 d

I want to find the users who has userid only 'a'. So in this case the desired output will be username2 and username6 Query 1:

select username from @t
except
select username from @t
where userid in('b','c','d')

Query 2:

select t1.username from @t t1
left join (select username from @t where userid in('b','c','d')) x
on x.username = t1.username
where x.username is null

Query 3:

select username from (
select username,

stuff((select ',' + CAST(t2.userid as varchar(max)) from @t t2
where t2.username = t1.username for xml path('')),1,1,'') as userids

from @t t1
group by username )X
where X.userids = 'a'

In all the 3 cases the output is username

username2
username6

Hope this helps :)

Niladri Biswas