?
Solved

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

Posted on 2008-11-05
8
Medium Priority
?
198 Views
Last Modified: 2012-05-05
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
Comment
Question by:Panos
  • 2
  • 2
  • 2
  • +2
8 Comments
 
LVL 60

Expert Comment

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

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

Accepted Solution

by:
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

by:chapmandew
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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
LVL 60

Assisted Solution

by:Kevin Cross
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

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


0
 
LVL 35

Expert Comment

by:YZlat
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

by:Panos
ID: 22886166
Hi to all of you.
Thank you for your help.
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.
Please help me with that too.
0
 
LVL 2

Author Closing Comment

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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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

809 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