Problem with query
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!
SonicMailer Pro - Professional mailing list manager & award-winning email marketing software
Download a FREE 30-day trial today!
Use coupon savemoreon4 and save 10%!
Greg K posted this at 06:24 — 17th February 2006.
He has: 2,145 posts
Joined: Nov 2003
From the query you have given, you are requesting that field_id be equal to 1 AND 2 at the same time and field_value be equal to 'aaa' AND 'bbb' at the same time.
I'm not exactly sure what you are wanting, but maybe replacing the middle AND with an OR ????
-Greg
Triexa.com posted this at 06:34 — 17th February 2006.
They have: 173 posts
Joined: Feb 2005
field_id references another table which has more information
but in my example.. 2 field_id's.. 1 and 2...
but I want the VALUE volumn of field_id's 1 & 2 to equal 'aaa' and 'bbb'... follow?
SonicMailer Pro - Professional mailing list manager & award-winning email marketing software
Download a FREE 30-day trial today!
Use coupon savemoreon4 and save 10%!
altweb posted this at 03:49 — 19th February 2006.
They have: 14 posts
Joined: Nov 2005
If I am reading your first post correctly...I think changing the AND to an OR will get the results you are looking for from your example. The OR will return a record if either is true.
Triexa.com posted this at 03:51 — 19th February 2006.
They have: 173 posts
Joined: Feb 2005
Umm no, I don't want either to be true.
Anyways, I ended up getting it through a series of INNER JOINS, and it seems to work just dandy!
Thanks to everyone, anyways
SonicMailer Pro - Professional mailing list manager & award-winning email marketing software
Download a FREE 30-day trial today!
Use coupon savemoreon4 and save 10%!
timjpriebe posted this at 13:38 — 21st February 2006.
He has: 2,667 posts
Joined: Dec 2004
If I understand correctly, you were wanting to check two records at once. They needed to have one record with one value, as well as another record with the second value.
I've tried that before and been unsuccessful. What was the solution you used?
Tim
http://www.tandswebdesign.com
Triexa.com posted this at 16:31 — 21st February 2006.
They have: 173 posts
Joined: Feb 2005
I joined the table for each additional field i wanted to check...
gretty posted this at 03:29 — 2nd April 2006.
They have: 9 posts
Joined: Apr 2006
can you do them all at once ?
Want to join the discussion? Create an account or log in if you already have one. Joining is fast, free and painless! We’ll even whisk you back here when you’ve finished.