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: http://dev.mysql.com/doc/refman/5.0/en/subqueries.html — Jai Govindani, Mar 31 '13 at 11:39

score 1 · Accepted Answer · answered Mar 31 '13 at 11:38

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 '13 at 11:38

John Woo

258,903
69
498
492

Thank you! I used the IN method, but why do you prefer JOIN? Thank you! :) – unicornication Mar 31 '13 at 12:29
1

[JOIN Clause vs. IN Clause](http://www.bennadel.com/blog/940-SQL-Optimization-Case-Study-JOIN-Clause-vs-IN-Clause.htm) and another one is here [SQL Join VS IN](http://stackoverflow.com/questions/1200295/sql-join-vs-in-performance) – John Woo Mar 31 '13 at 12:30

score 1 · Answer 2 · answered Mar 31 '13 at 11:43

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 '13 at 11:43

Bohemian

412,405
93
575
722

Using MySQL return data as input for 2nd query?

2 Answers2