SQL help (possible a join)

Discussion in 'Scripts, 3rd Party Apps, and Programming' started by callumacrae, Apr 14, 2011.

  1. callumacrae

    callumacrae not alex mac Community Support

    Messages:
    5,257
    Likes Received:
    97
    Trophy Points:
    48
    I have two tables:

    feed:
    - id
    - user_id
    - content

    friends:
    - id
    - user_id
    - user2_id

    Say (for example) that friends contains the following data:

    0, 1, 4
    1, 1, 6
    2, 1, 12
    3, 6, 1
    3, 4, 1
    3, 1, 15
    3, 12, 6

    feed contains the following data:

    0, 1, 'Lorem Ipsum'
    1, 2, 'Hello world!'
    2, 1, 'What?'
    3, 4, 'Bla'
    4, 7, 'Test'

    I want to get all items from the feed that are from friends. For example, if the current user id is 1, I want to get all the feed info from users 4, 6, 12 and 15 (and themselves):

    0, 1, 'Lorem Ipsum'
    2, 1, 'What?'
    3, 4, 'Bla'


    How would I do this using SQL? It should be portable SQL, usable in MySQL, Firebird etc.

    ~Callum
     
  2. gomarc

    gomarc Member

    Messages:
    516
    Likes Received:
    18
    Trophy Points:
    18
    Hi callumacrae,


    I would run 2 separate queries. The first one will get the user results:

    Code:
    
    SELECT id, user_id, content
    FROM feed
    WHERE feed.user_id = ?
    
    
    And the second one for the user's friends:

    Code:
    
    SELECT feed.id,
           feed.user_id,
           feed.content
    FROM feed
    INNER JOIN friends
    ON friends.user2_id = feed.user_id
    WHERE friends.user_id = ?
    ORDER BY feed.id ASC
    LIMIT 10
    
    
    Save both of the results into an array and there you go.
     
  3. callumacrae

    callumacrae not alex mac Community Support

    Messages:
    5,257
    Likes Received:
    97
    Trophy Points:
    48
    Can I do an OR on the inner join?

    ~Callum
     
  4. descalzo

    descalzo Grim Squeaker Community Support

    Messages:
    9,375
    Likes Received:
    327
    Trophy Points:
    83
    Code:
    
    SELECT feed.id , feed.user_id, feed.content 
        FROM feed INNER JOIN friends 
        ON feed.user_id = friends.user2_id  
        WHERE friends.user_id = current_user ;
    should work

    Edit: facepalm.png ... thirty lashes with a wet lasagna noodle for me for not reading gomarc's post more closely.
     
    Last edited: Apr 14, 2011
  5. callumacrae

    callumacrae not alex mac Community Support

    Messages:
    5,257
    Likes Received:
    97
    Trophy Points:
    48
    :/

    Well this is awkward...

    ~Callum
     
  6. gomarc

    gomarc Member

    Messages:
    516
    Likes Received:
    18
    Trophy Points:
    18
    Yes you can use the SQL UNION operator to combine the results of two queries into a composite result.

    It will be something like this:

    Code:
    
    SELECT feed.id, feed.user_id, feed.content
    FROM feed
    WHERE feed.user_id = ?
    UNION
    SELECT feed.id, feed.user_id, feed.content
    FROM feed
    INNER JOIN friends ON friends.user2_id = feed.user_id
    WHERE friends.user_id = ?
    
    
     
  7. callumacrae

    callumacrae not alex mac Community Support

    Messages:
    5,257
    Likes Received:
    97
    Trophy Points:
    48
    Will that return them in proper order or will it be all the users stuff and then their friends stuff?

    ~Callum
     
  8. callumacrae

    callumacrae not alex mac Community Support

    Messages:
    5,257
    Likes Received:
    97
    Trophy Points:
    48
    Will that return them in proper order or will it be all the users stuff and then their friends stuff?

    ~Callum
     
  9. gomarc

    gomarc Member

    Messages:
    516
    Likes Received:
    18
    Trophy Points:
    18
    callumacrae,

    As posted, the query has no defined order. You need to add 'ORDER BY'.

    If you want it by feed.id ASC:

    Code:
    
    SELECT feed.id, feed.user_id, feed.content
    FROM feed
    WHERE feed.user_id = ?
    UNION
    SELECT feed.id, feed.user_id, feed.content
    FROM feed
    INNER JOIN friends ON friends.user2_id = feed.user_id
    WHERE friends.user_id = ?
    ORDER BY id ASC
    
    
     
  10. callumacrae

    callumacrae not alex mac Community Support

    Messages:
    5,257
    Likes Received:
    97
    Trophy Points:
    48
    Will that affect the entire union or just the second statement?
     
  11. gomarc

    gomarc Member

    Messages:
    516
    Likes Received:
    18
    Trophy Points:
    18
    All of it. You can test yourself by changing ASC to DESC.
     
  12. denzil

    denzil New Member

    Messages:
    134
    Likes Received:
    3
    Trophy Points:
    0
    Callum. I would just like to point out that you could potentially drop the ID table from friends, and set your primary key to the combination of user_id and user2_id.
     
  13. callumacrae

    callumacrae not alex mac Community Support

    Messages:
    5,257
    Likes Received:
    97
    Trophy Points:
    48
    How would I go about doing that? I've known that it's possible, but never actually tried it
     
  14. misson

    misson Community Paragon Community Support

    Messages:
    2,572
    Likes Received:
    72
    Trophy Points:
    48
    Code:
    ALTER TABLE friends DROP COLUMN id;
    ALTER TABLE friends ADD PRIMARY KEY (user_id, user2_id);
    
    To do it from scratch:

    Code:
    CREATE TABLE friends (
        user_id INT UNSIGNED,  -- Match dfn of users(id); should only be UNSIGNED if users (id)
        user2_id INT UNSIGNED, --  is unsigned, which it should be if auto-incremented
        PRIMARY KEY (user_id, user2_id),
        FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE ON UPDATE CASCADE,
        FOREIGN KEY (user2_id) REFERENCES users (id) ON DELETE CASCADE ON UPDATE CASCADE
    ) Engine=InnoDB; -- in MySQL, must set Engine for foreign keys to work.
    
    Some RDBMSs (e.g. MySQL and SQL Server) support REFERENCES attributes, meaning you can place the FOREIGN KEY constraints in the column definition:
    Code:
        user_id INT UNSIGNED REFERENCES users (id) ON DELETE CASCADE ON UPDATE CASCADE,
    
    However, this is less portable, and MySQL will ignore the foreign key attributes if there are any problems (such as column type mismatches).

    Functionally, primary keys are simply NOT NULL columns with a UNIQUE index. If you already have a primary key defined on a table, you can define additional non-null, unique columns.

    Yes, you can use OR in the join condition, or you could do it in the WHERE clause:
    Code:
    SELECT DISTINCT feed.*
      FROM feed
        JOIN friends ON feed.user_id=friends.user2_id
      WHERE friends.user_id=? OR friends.user2_id=?
      ORDER BY feed.id
    ;
    As for which query to use, you'll need to profile each (after making sure you have suitable indices defined on your tables) to see which performs better. Every RDBMS has it's own method of profiling. In MySQL, for example, you'd use EXPLAIN.
     

Share This Page