SQL help (possible a join)

[callumacrae]

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]

Hi callumacrae,


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

SELECT id, user_id, content
FROM feed
WHERE feed.user_id = ?

And the second one for the user's friends:

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.

[callumacrae]

Can I do an OR on the inner join?

~Callum

[descalzo]

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.

[callumacrae]

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]

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:

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]

Will that return them in proper order or will it be all the users stuff and then their friends stuff?

~Callum

[callumacrae]

Will that return them in proper order or will it be all the users stuff and then their friends stuff?

~Callum

[gomarc]

callumacrae,

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

If you want it by feed.id ASC:

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]

Will that affect the entire union or just the second statement?