CODEWITHSUNDEEP
phpmysqlsqljoin
cdub
cdub

Reputation: 25741

Complex SQL (Maybe Outer Joins)

I have 2 tables as defined below.

Reviews table
id
user_id
review
title
datetime

PeopleFollows table
id
user_id
following_user_id
datetime

I want to run 1 query to get the top 10 results by datetime. So for instance, if I have the following data:

Review table 

1 user1  "This is my review" "title" 2011-01-10  
2 user1 "Another review" "title again" 2011-01-08

People Follows table

1 user2 user1 2011-01-09

I want to get the results like this:

Review id=2  
People follows id=1
Review id = 1

The only way I think I can do this is to do separate queries of X limit, then combine them to get X results.

I might need to explain a little more.

Upvotes: 0

Views: 1047

Answers (2)

Multifarious
Multifarious

Reputation: 216

You don't need full scans if you index both of the tables by datetime and also order each query by datetime before UNION.

(SELECT "Review", id, datetime FROM Reviews ORDER BY datetime DESC LIMIT 10)
UNION 
(SELECT "People", id, datetime FROM PeopleFollows ORDER BY datetime DESC LIMIT 10)
ORDER BY datetime DESC
LIMIT 10

Upvotes: 2

Oroboros102
Oroboros102

Reputation: 2254

You can use UNION, but query won't be optimal:

(SELECT "Review", id FROM Reviews)
UNION
(SELECT "People", folowing_user_id FROM PeopleFollows)
ORDER BY datetime DESC
LIMIT 10

You can not use proper indexes in this query, it would be fullscan. So, use it at your own risk.

Better idea - not to try to merge tables. Use third cache table for this.

Upvotes: 0

Related Questions