Checking for multiple values with in same column

Question

This is how my database table structure looks -

ID fieldname fieldvalue
1   country   USA
2   language  English
3   country   India
4   language  Hindi

So I want to select the rows where the fieldname is say 'country' and fieldvalue is USA AND fieldname is language and fieldvalue is English.

Basically filtering by multiple values bases on key / value pairs.

I've tried using the AND keyword but its returns nothing

Any clue ?

Such generic design is a database design one would avoid if not really, really necessary. Maybe you should change your design, so writing queries would be much easier. — Thorsten Kettner
– Thorsten Kettner, Commented May 13, 2015 at 12:45

Robert · Accepted Answer · 2015-05-13 12:20:24Z

4

Try to use OR operator as below

select *
from tab
where (fieldname = 'country' and  fieldvalue = 'USA')
or (fieldname = 'language' and  fieldvalue = 'English')

edited May 13, 2015 at 12:20

answered May 13, 2015 at 12:14

Robert

25.8k8 gold badges70 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

user2475624 Over a year ago

Yes, but the issue is it would return either country is USA or language is English, right ?

user2475624 Over a year ago

@nope, this returns the items if either field name is there or fieldvalue.

Robert Over a year ago

@user2475624 What do you expect in result?

Robert Over a year ago

@user2475624 So it's totally different problem than you asked. I need to see a query than I can help you.

Thorsten Kettner Over a year ago

@user2475624: No, you shouldn't continue in chat. You've asked a question and got an answer, and you should accept it. As Parado just said, the question you are about to ask now is completely different from the question at hand, so you should better ask a new question in a new request.

|

massie · Accepted Answer · 2015-05-13 12:16:12Z

2

A fieldname can't be both 'country' AND 'language' at the same time. But if you use OR, they are both true.

SELECT *
FROM table
WHERE ( fieldname = 'country' AND fieldvalue = 'USA' )
   OR ( fieldname = 'language' AND fieldvalue = 'English');

answered May 13, 2015 at 12:16

massie

5562 silver badges10 bronze badges

Collectives™ on Stack Overflow

Checking for multiple values with in same column

2 Answers 2

8 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related