CODEWITHSUNDEEP
mysql
unicornication
unicornication

Reputation: 627

Using MySQL return data as input for 2nd query?

I'm trying to build a 'news feed' of sorts, and I'm having trouble writing up the query in order to do this!

The question in a nutshell, if User A goes to their home page and follows Users B, C, D - I'm trying to make it so that they see content from users B, C, D.

Assuming 2 tables: posts, connections

Where table posts has lots of relevant columns, and table connections has columns id,user,followed

In (terrible) pseudocode, I'm trying to do something like this:

SELECT * FROM posts WHERE author=(SELECT followed FROM connections WHERE user='A')

And it'd post stuff by B,C,D (aka who A follows)

By any chance would anyone know how to write this as an actual MySQL query? The stuff I've read already seems pretty confusing, and I don't really understand how I would go about doing this!

Thank you, very much! Any help is greatly appreciated! :)

Upvotes: 0

Views: 37

Answers (2)

Bohemian
Bohemian

Reputation: 425198

Use a join:

SELECT p.*
FROM connections c
JOIN posts p
  ON c.followed = p.author
WHERE user='A'

Note that the connections table is listed first, so an index on user could be used driven by the where clause.

Upvotes: 1

John Woo
John Woo

Reputation: 263803

You can use IN

SELECT * 
FROM   posts 
WHERE  author IN (SELECT followed FROM connections WHERE user = 'A')

or JOIN (which I preferred)

SELECT  DISTINCT a.*
FROM    posts a
        INNER JOIN connections b
            ON a.author = b.followed
WHERE   b.user = 'A'

Upvotes: 1

Related Questions