Problem with query

#native_company# #native_desc#

#native_cta#

This site is best viewed in a modern browser with JavaScript enabled.

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') [color=red]OR[/color] (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

sdesigns wrote:
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.