Problem with query
I have a table of custom data (called fields)
field_id, user_id, field_value
lets just say:
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 a value of 'aaa' and also has a field_value 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')
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')
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... ?
field_id, userid, value
so I would only want to get user 1 ebcause they amtch both 'aaa' and 'bbb', but user 2 does not...
Last edited by sdesigns; 02-16-2006 at 10:45 PM.
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..
no that.. won't work
at least not how i understand it.
f1 and f2... i don't quite follow but.. ya
you want to fetch all those users (userid) who have both the values 'aaa' and 'bbb'. right.
Originally Posted by sdesigns
i'm not sure wat are trying to achieve thru field_id in this case..
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.
chazzy -- I am not sure I totally follow you. Could you explain a bit more or show some code?
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.
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)