SQL help (possible a join)

callumacrae

not alex mac
Community Support
Messages
5,257
Reaction score
97
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
 

gomarc

Member
Messages
516
Reaction score
18
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.
 

descalzo

Grim Squeaker
Community Support
Messages
9,373
Reaction score
326
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:

callumacrae

not alex mac
Community Support
Messages
5,257
Reaction score
97
Points
48
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

:/

Well this is awkward...

~Callum
 

gomarc

Member
Messages
516
Reaction score
18
Points
18
Can I do an OR on the inner join?

~Callum

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 = ?
 

callumacrae

not alex mac
Community Support
Messages
5,257
Reaction score
97
Points
48
Will that return them in proper order or will it be all the users stuff and then their friends stuff?

~Callum
 

callumacrae

not alex mac
Community Support
Messages
5,257
Reaction score
97
Points
48
Will that return them in proper order or will it be all the users stuff and then their friends stuff?

~Callum
 

gomarc

Member
Messages
516
Reaction score
18
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
 

callumacrae

not alex mac
Community Support
Messages
5,257
Reaction score
97
Points
48
Will that affect the entire union or just the second statement?
 

denzil

New Member
Messages
134
Reaction score
3
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.
 

callumacrae

not alex mac
Community Support
Messages
5,257
Reaction score
97
Points
48
How would I go about doing that? I've known that it's possible, but never actually tried it
 

misson

Community Paragon
Community Support
Messages
2,572
Reaction score
72
Points
48
How would I go about doing that? I've known that it's possible, but never actually tried it

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.

Can I do an OR on the inner join?
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.
 
Top