Geek asks and answers

How to get 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 create a sql query that will find all users with fieldvalueequal to something for the selectedfieldid

For example, for values

fieldid: 817
fieldvalue: 'yes'

I may have a sql query, for example:

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

This request is working fine.

However, if I have a second or third criterion by making a request for example:

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

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

Is there any way to fix this?

Update

I forgot to record a precedent (sentences)

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 need a sql query that finds users who meet the following conditions: field value 817 is yes and fieldid 818 is no

OR, fieldid 817, yes, fieldid 818, no

, .

userid
1


userid
1
2

userid 2 . .

+3
6

OR .

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

AND/OR

AND , , .

OR , , .

EDIT: .

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')

. sqlfiddle

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'

sqlfiddle

, , :

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')
+2

OR

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

, fieldid 817 818, .

0

OR not AND WHERE. .

0

. , OR AND

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

AND, , fieldid=817 AND fieldvalue='yes' ANd fieldid=818 AND fieldvalue='no', . ANd, OR , , .

0

In addition to numerous usage suggestions ORthat are completely correct, you can also use IN(which can make the request more readable if it scales).

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

To find everything useridthat satisfies conditions like , you need to independently attach tableto yourself:

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' )
0
source

you need to use it like:

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

More articles:


All Articles