Help with syntax needed 1

[abenstex]

Hello

i have a table consisting of word_nr (key) and example_nr. Now i want to find out how many entries for example_nr are returned where word_nr=x and word_nr=y. So i made something like:

SELECT count(*)
FROM mytable WHERE word_nr=x and word_nr=y

Even though that i know that there is at least one entry for example_nr with word_nr = x and word_nr = y, the result for the query is 0. Am i doing something wrong here?

 

[TonyGroves]

Change AND to OR.

word_nr can't be equal to x AND y at the same time!

 

[r937]

if you want to know which example_nr has both an x row and a y row, you need to use GROUP BY --

select example_nr 
  from mytable 
 where word_nr in ( [i]x[/i] , [i]y[/i] )
group
    by example_nr 
having count(*) = 2

a similar scenario is a job candidate skills table, where each row represents a single skill that a candidate has

in the following query, you are looking for candidates who have at least 3 of the 4 stated skills --

select candidate_id 
  from skillz
 where skill in ( 'HTML','ColdFusion','CSS','MySQL' )
group
    by candidate_id  
having count(*) >= 3

in your example, you want both x AND y, so you test for count(*)=2



rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)