CODEWITHSUNDEEP
mysqlsql
nkorf
nkorf

Reputation: 59

How do I fetch query results with multiple criteria?

I have a table like this

Table
-----
userid
fieldid
fieldvalue

where userid and fieldid are the primary key pair for this table.

I want to make an sql query that finds all users that have fieldvalue equal to something for a selected fieldid

For example, for the values

fieldid: 817
fieldvalue: 'yes'

I can have an sql query like:

select userid FROM table where (fieldid=817 AND fieldvalue='yes')

This query works fine.

However if i have a second or a third criterion, making the query like this:

select userid 
FROM table 
where (fieldid=817 AND fieldvalue='yes')
AND (fieldid=818 AND fieldvalue='no')

returns an empty result but the conditions are satisfied in the individual criterion.

Is there any way to correct this ?

update

i forgot to write down a use case (appologies)

userid, fieldid, fieldvalue
1 , 817, yes
1, 818, no
1, 825, yes 
2, 817, yes 
2, 818, yes
3, 829, no

for this table i want an sql query that finds the users that have the following conditions satisfied : The fieldid 817 has a value of yes and the fieldid 818 a value of no

Using the OR suggestions i had so far satisfied either the fieldid 817 to have a value of yes OR the fieldid 818 to have a value of no

I want both conditions to be satisfied. In the above the expected result will be

userid
1

and not

userid
1
2

Since userid 2 doesn't satisfy both conditions. Apologies for the confusion.

Upvotes: 3

Views: 1798

Answers (6)

Taryn
Taryn

Reputation: 247710

You should use an OR between your different criteria.

SELECT userid 
FROM table 
WHERE (fieldid=817 AND fieldvalue='yes')
    OR (fieldid=818 AND fieldvalue='no')

The difference between using AND/OR

The AND operator displays a record if both the first condition and the second condition is true.

The OR operator displays a record if either the first condition or the second condition is true.

EDIT: Based on your comments you can do it the following way

select t1.userid 
FROM temp t1
where (t1.fieldid=817 AND t1.fieldvalue='yes') 
    AND EXISTS (SELECT userid 
            FROM temp t 
            WHERE t.userid =  t1.userid 
                AND fieldid=818 
                AND fieldvalue='no')

see a sqlfiddle with a working copy

or even this way

select t1.userid 
FROM temp t1
left join temp t2
    on t1.userid = t2.userid
where (t1.fieldid=817 AND t1.fieldvalue='yes')
    AND t2.fieldid=818 AND t2.fieldvalue='no'

here is another sqlfiddle

if you had more fields that you wanted to join on, then you would do the following:

select t1.userid 
FROM temp t1
left join temp t2
    on t1.userid = t2.userid
left join temp t3
    on t1.userid = t3.userid
where (t1.fieldid=817 AND t1.fieldvalue='yes')
    AND (t2.fieldid=818 AND t2.fieldvalue='no')
    AND (t3.fieldid=819 AND t3.fieldvalue='no')

Upvotes: 2

eggyal
eggyal

Reputation: 125865

In addition to the many suggestions of using OR, which are entirely correct, you could also use IN (which might make the query more readable if it scales).

SELECT userid FROM table WHERE (fieldid, fieldvalue) IN (
  (817, 'yes'),
  (818, 'no' )
);

To find all userid that satisfy both conditions, you need to self-join table to itself:

SELECT userid
FROM table AS t1 JOIN table AS t2 USING (userid)
WHERE
      (t1.fieldid = '817' AND t1.fieldvalue = 'yes')
  AND (t2.fieldid = '818' AND t2.fieldvalue = 'no' )

Upvotes: 0

orif
orif

Reputation: 362

you need to use it like:

select userid 
FROM table 
where fieldid in (817, 818) and fieldvalue in ('yes', 'no');

Upvotes: 0

Er. Anurag Jain
Er. Anurag Jain

Reputation: 1793

Hi Please try OR in Place of AND

select userid FROM table where (fieldid=817 AND fieldvalue='yes') OR (fieldid=818 AND fieldvalue='no')

If you use AND then it will check if both conditions fieldid=817 AND fieldvalue='yes' ANd fieldid=818 AND fieldvalue='no' must be fulfilled but it is not possible. ANd when you will use OR operator between both conditions then it will give result having both conditions ..

thanks

Upvotes: 0

Joseph Victor Zammit
Joseph Victor Zammit

Reputation: 15310

You should use OR not AND in your WHERE clause. Check out this link for a quick-and-easy explanation.

Upvotes: 0

taylonr
taylonr

Reputation: 10790

Try using OR

select userid FROM table where (fieldid=817 AND fieldvalue='yes') OR(fieldid=818 AND fieldvalue='no')

Your query wanted the fieldid to be 817 and 818 which isn't possible.

Upvotes: 0

Related Questions