SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Guru
    Join Date
    Dec 2005
    Posts
    964
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Find where users match!?

    I have some db_table data like this:

    MyTable:
    id fk_user_id fk_data_id
    1 1 1
    2 2 1
    3 1 2
    4 3 2
    5 2 3
    6 3 3
    7 1 4
    8 2 4


    Now what I want to do is to get the fk_data_id from the table where fk_user_id 1 AND 2 matches (That would in this case be 1 and 4). I want to get them out in an array but not sure how to do so?

    Can anyone please help... Thank in advance :-)

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,246
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Code:
    SELECT fk_data_id
      FROM db_table 
     WHERE fk_user_id IN ( 1,2 ) -- list of users
    GROUP
        BY fk_data_id
    HAVING COUNT(*) >= 2 -- number of users
    note i used >=2 since you are using an auto_increment column in that table, and i'll bet you haven't ensured the uniqueness of the user/data combos

    if you don't understand what i just said, please at least confirm that the query works
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Guru
    Join Date
    Dec 2005
    Posts
    964
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    Code:
    SELECT fk_data_id
      FROM db_table 
     WHERE fk_user_id IN ( 1,2 ) -- list of users
    GROUP
        BY fk_data_id
    HAVING COUNT(*) >= 2 -- number of users
    note i used >=2 since you are using an auto_increment column in that table, and i'll bet you haven't ensured the uniqueness of the user/data combos

    if you don't understand what i just said, please at least confirm that the query works
    Works like a charm... Just what I was looking for ;-) Thanks and I do understand...


Bookmarks

Posting Permissions

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