Solved

I don't get results using 'WHERE' and 'AND'

Posted on 2008-11-05
Medium Priority
198 Views
Hello experts
I have a table Artextras
Artikel_ID     Extras
253           10
256           20
264           20
264          10
264          40
264          50
264          60

And when i run the query:
SELECT *
FROM Artextras
WHERE Extras = 10
AND Extras = 20
i don't get any results.I must get the Artikel_id 264 as result .where is the mistake?
0
Question by:Panos
• 2
• 2
• 2
• +2

LVL 60

Expert Comment

ID: 22885385
you shoudln't be using AND...use OR

SELECT *
FROM Artextras
WHERE Extras IN (10, 20)
0

LVL 37

Accepted Solution

momi_sabag earned 1000 total points
ID: 22885387
you don't have a result since no rows have a extras=20 and extras=10
you want this

select t1.artikel_id
from Artextras t1 inner join Artextras t2 on t1.artikel_id = t2.artikel_id
where t1.Extras = 10
and t2.Extras = 20
0

LVL 60

Expert Comment

ID: 22885392
logically...AND would never work here on the same field.  One field can never have 2 specific values (for the same "cell")
0

LVL 60

Assisted Solution

Kevin Cross earned 1000 total points
ID: 22885814
The answers above are correct --- this is an alternative to get what I think you are after.

You want to select id that has a record for 10 and for 20 and only those ids that do.

SELECT artikel_id
FROM Artextras a
WHERE extras = 10 AND EXISTS (SELECT 1 FROM Artextras b WHERE a.artikel_id = b.artikel_id AND extras = 20)

0

LVL 35

Expert Comment

ID: 22885935
SELECT *
FROM Artextras
WHERE Extras = 10 OR Extras = 20

0

LVL 35

Expert Comment

ID: 22885955
SELECT *
FROM Artextras
WHERE Extras = 10
AND Extras = 20

will not return any results because no field Extras is both 10 and 20. You have to use OR:

SELECT *
FROM Artextras
WHERE Extras = 10 OR Extras = 20

this will return

253           10
256           20
264           20
264          10

0

LVL 2

Author Comment

ID: 22886166
Hi to all of you.
looking at the question .....I must get the Artikel_id 264 as result .where is the mistake?......
i think that mwvisa1 and momi_sabag have found the way to get only 264 as result.
Now i will make a new question where these two numbers are not always the same but a variable.
0

LVL 2

Author Closing Comment

ID: 31513489
Thank you again for your help.
Regards
Panos
0

Featured Post

Question has a verified solution.

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

In this article we will learn how to fix  āCannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi fileā error ?
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began apā¦
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses
Course of the Month13 days, 16 hours left to enroll