sql request - multiple value

Question

I have the following table:

userid    title         content
1         gender        male
1         location      NY    
2         gender        female
2         location      SF
3         gender        female
3         location      NY

I'm trying to retrieve only userid with gender="male" and location="NY"

if I try:

select userid
from table
where content="male"
   AND location="NY";

It will return null. Any idea how to do that?

BTW: This is a bad idea for a table structure. If at all possible I'd suggest rearchitecting it. — JohnFx
– JohnFx, Commented Nov 30, 2011 at 2:55

Adam Wenger · Accepted Answer · 2011-11-30 02:53:41Z

1

SELECT t.userId
FROM yourTable AS t
INNER JOIN yourTable AS t2 ON t.userId = t2.userId
   AND t.title = 'gender'
   AND t.content = 'male'
   AND t2.title = 'location'
   AND t2.content = 'NY'

I would also consider looking at normalizing your data. It would make queries like this much easier (and probably faster) in the future.

answered Nov 30, 2011 at 2:53

Adam Wenger

17.7k8 gold badges54 silver badges65 bronze badges

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

2 Comments

JohnFx Over a year ago

beat me by 35 seconds. Hate when that happens. Need to learn to type faster on these softball questions. +1

Adam Wenger Over a year ago

@JohnFx I definitely know how that goes, I've been too slow on many questions already tonight.

Collectives™ on Stack Overflow

sql request - multiple value

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related