www.webdeveloper.com
Results 1 to 10 of 10

Thread: Problem with query

  1. #1
    Join Date
    Jul 2004
    Posts
    30

    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!

  2. #2
    Join Date
    Aug 2005
    Location
    The Garden State
    Posts
    5,634
    can't do that.

  3. #3
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,633
    I'm just guessing that what you really mean is:
    Code:
    WHERE (f.field_id=1 AND f.field_value = 'aaa') OR (f.field_id=2 AND f.field_value = 'bbb')
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  4. #4
    Join Date
    Jul 2004
    Posts
    30
    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...
    Last edited by sdesigns; 02-16-2006 at 11:45 PM.

  5. #5
    Join Date
    Dec 2005
    Location
    India
    Posts
    490
    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..

  6. #6
    Join Date
    Jul 2004
    Posts
    30
    no that.. won't work

    at least not how i understand it.

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

  7. #7
    Join Date
    Dec 2005
    Location
    India
    Posts
    490
    Quote Originally Posted by sdesigns
    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..

  8. #8
    Join Date
    Aug 2005
    Location
    The Garden State
    Posts
    5,634
    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.

  9. #9
    Join Date
    Jul 2004
    Posts
    30
    chazzy -- I am not sure I totally follow you. Could you explain a bit more or show some code?

    thanks

  10. #10
    Join Date
    Aug 2005
    Location
    The Garden State
    Posts
    5,634
    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.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles