CODEWITHSUNDEEP
mysqlsqljoin
Roy
Roy

Reputation: 3267

Joining two select queries and ordering results

Basically I'm just unsure as to why this query is failing to execute:

(SELECT replies.reply_post, replies.reply_content, replies.reply_date AS d, members.username
     FROM (replies) AS a 
        INNER JOIN members ON replies.reply_by = members.id)

            UNION

(SELECT posts.post_id, posts.post_title, posts.post_date AS d, members.username 
     FROM (posts) as b 
        WHERE posts.post_set = 0 
           INNER JOIN members ON posts.post_by = members.id)

ORDER BY d DESC LIMIT 5

I'm getting this error:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'a INNER JOIN members ON replies.re' at line 2

All I'm trying to do is select the 5 most recent rows (dates) from these two tables. I've tried Join, union etc and I've seen numerous queries where people have put another query after the FROM statement and that just makes no logical sense to me as to how that works?

Am I safe to say that you can join the same table from two different but joined queries? Or am I taking completely the wrong approach, because frankly I can't seem see how this query is failing despite reading the error message.

(The two queries on there own work fine)

Upvotes: 0

Views: 80

Answers (2)

Upendra Chaudhari
Upendra Chaudhari

Reputation: 6543

I think there is syntax error in your query at below part :

FROM (posts) as b 
        WHERE posts.post_set = 0 
           INNER JOIN members ON posts.post_by = members.id)

Inner join should come first before where condition. Also your join conditions are wrong. You need to apply conditions like 

INNER JOIN members ON a.reply_by = members.id)
INNER JOIN members ON b.post_by = members.id)

So your query should be like this 

(SELECT a.reply_post, a.reply_content, a.reply_date AS d, members.username
 FROM (replies) AS a 
 INNER JOIN members ON a.reply_by = members.id)

 UNION

(SELECT b.post_id, b.post_title, b.post_date AS d, members.username 
 FROM (posts) as b 
 INNER JOIN members ON b.post_by = members.id
 WHERE b.post_set = 0)

ORDER BY d DESC LIMIT 5

Upvotes: 1

Joke_Sense10
Joke_Sense10

Reputation: 5402

Try this:

(SELECT a.reply_post, a.reply_content, a.reply_date AS d, members.username
 FROM replies AS a 
    INNER JOIN members ON a.reply_by = members.id)

        UNION

(SELECT b.post_id, b.post_title, b.post_date AS d, members.username 
 FROM posts as b 
    INNER JOIN members ON b.post_by = members.id 
    WHERE b.post_set = 0)  /* Use where condition after matching Id's using ON */

ORDER BY d DESC LIMIT 5

Upvotes: 1

Related Questions