CODEWITHSUNDEEP
phpmysqlselect
Mato
Mato

Reputation: 39

Select newest records from multiple tables

I have three tables in mysql database:

Flats [id, section, title, description, rooms, price, updated]

Houses [id, section, title, description, rooms, price, updated]

Lands [id, section, title, description, area, price, updated]

I need to retrieve four newest records from all records in these tables in one query (id, title) and I don't know how to write query correctly. I've tried this:

    SELECT                                 
       flats.id, flats.title, houses.id, houses.title, lands.id, lands.title
    FROM 
       flats, houses, lands
    ORDER BY
       updated DESC
    LIMIT
       3

Thanks for all replies.

Upvotes: 1

Views: 48

Answers (3)

LukasS
LukasS

Reputation: 176

In MySQL most common way to do that is to use UNION statement.

So, in your case:

(SELECT id,title from flats ORDER BY updated DESC LIMIT 4) UNION (SELECT id,title from houses ORDER BY updated DESC LIMIT 4) UNION (SELECT id,title from lands ORDER BY updated DESC LIMIT 4);

More info http://dev.mysql.com/doc/refman/5.7/en/union.html

Upvotes: 0

Michael
Michael

Reputation: 331

SELECT                                 
   flats.id, flats.title, houses.id, houses.title, lands.id, lands.title
FROM 
   flats
CROSS JOIN houses
CROSS JOIN lands
ORDER BY
   updated DESC
LIMIT
   3

But this is not good tactics :D I guess, if you shuld do something like this

Upvotes: 1

Mureinik
Mureinik

Reputation: 311163

You can use the union all operator to "concat" the results, and then use a limit clause to get the top four:

SELECT  id, title
FROM    (SELECT id, title, updated FROM flats
         UNION ALL
         SELECT id, title, updated FROM houses
         UNION ALL
         SELECT id, title, updated FROM lands) t
ORDER BY updated DESC
LIMIT    4

EDIT:
As pointed out by Michael and John in the comments, the subquery is redundant:

SELECT   id, title, updated FROM flats
UNION ALL
SELECT   id, title, updated FROM houses
UNION ALL
SELECT   id, title, updated FROM lands
ORDER BY updated DESC
LIMIT    4

Upvotes: 3

Related Questions