Using MySQL return data as input for 2nd query?

Question

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! :)

You mean a subquery, such as: SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2); Check out: dev.mysql.com/doc/refman/5.0/en/subqueries.html — Jai Govindani
– Jai Govindani, Commented Mar 31, 2013 at 11:39

John Woo · Accepted Answer · 2013-03-31 11:38:58Z

1

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'

answered Mar 31, 2013 at 11:38

John Woo

265k70 gold badges509 silver badges500 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

unicornication Over a year ago

Thank you! I used the IN method, but why do you prefer JOIN? Thank you! :)

John Woo Over a year ago

JOIN Clause vs. IN Clause and another one is here SQL Join VS IN

Bohemian · Accepted Answer · 2013-03-31 11:43:16Z

1

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.

answered Mar 31, 2013 at 11:43

Bohemian♦

427k103 gold badges603 silver badges750 bronze badges

Collectives™ on Stack Overflow

Using MySQL return data as input for 2nd query?

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related