Problem with query

[sdesigns]

I have a table of custom data (called fields)
field_id, user_id, field_value

lets just say:
row 1
field_id=1
user_id=2
field_value ='aaa'

row 2
field_id=2
user_id=2
field_value = 'bbb'

I am having problems with the WHERE part of my clause.. I want to select all rows where the user has field_id[1] a value of 'aaa' and also has a field_value[2] of 'bbb'...

WHERE (f.field_id=1 AND f.field_value = 'aaa') AND (f.field_id=2 AND f.field_value = 'bbb')

hope someone can help me make it work, thanks!!


The full query:
SELECT u.* FROM users AS u, fields AS f WHERE (f.field_id=1 AND f.field_value = 'aaa') AND (f.field_id=2 AND f.field_value = 'bbb')

thankss!

[chazzy]

can't do that.

[NogDog]

I'm just guessing that what you really mean is:

WHERE (f.field_id=1 AND f.field_value = 'aaa') OR (f.field_id=2 AND f.field_value = 'bbb')

[sdesigns]

OR works...

but I want the condition to match...

I want to select all users who match a number of conditions on this second table.

In that example, I want BOTH of the subscriber's fields (rows 1 and 2) to match 'aaa' and 'bbb' respectively... not either or... ?


table users
id, email
1, a@b.c
2, d@e.f

table fields
field_id, userid, value
1,1,'aaa'
2,1,'bbb'
1,2,'aaa'
2,2,'ccc'

so I would only want to get user 1 ebcause they amtch both 'aaa' and 'bbb', but user 2 does not...

[sridhar_423]

try this one.. ( i hope undestood correctly.. all ur userid and field_id values with just 1s and 2s is confusing)

select * from users where userid in(select f1.userid from users f1,users f2 where f1.userid=f2.userid and f1.value='aaa' and f2.VALUE='bbb')

it might be complicated. i dont know if there's a simpler query for this..

[sdesigns]

no that.. won't work

at least not how i understand it.

f1 and f2... i don't quite follow but.. ya

[sridhar_423]

so I would only want to get user 1 ebcause they amtch both 'aaa' and 'bbb', but user 2 does not...

you want to fetch all those users (userid) who have both the values 'aaa' and 'bbb'. right.
i'm not sure wat are trying to achieve thru field_id in this case..

[chazzy]

As I stated, you can't do that.

YOu should create a view that is a join on this table w/ itself to see all pairs of attributes for the same user. Then compare that view to your user. You can't analyze two rows - its illogical. When you create the view, it'll have 1 user with both attributes, then you can see who has both and if your user has it.

[sdesigns]

chazzy -- I am not sure I totally follow you. Could you explain a bit more or show some code?

thanks

[chazzy]

Basically, you are trying to find a user that has a particular 2 rows of data. Normally, you can't do that, you only do row by row comparisons. In order to emulate this, you need to create a view - where it will show the user, column_a, column_b, the 2nd column_a and the second column_b. You know what a view is, right?

You then check that the first field is 'aaa' and the second is 'bbb' and it should work.