CODEWITHSUNDEEP
phpmysqlsqlpdo
Aaron Wojnowski
Aaron Wojnowski

Reputation: 6480

MySQL Combine Query Results From Multiple Tables

Suppose I have two tables: shares and follows. Currently, I have a query that returns the latest shares ordered by date, limited to 25 results. What I would like to do is query from both follows and shares, returning the latest 25 results. However, I'm unsure as to how to do this.

My query for the shares is like so:

select s.id, s.name, s.sharer, s.type from shares s where id = :id order by s.date desc limit 0,25

My query for the followers is like so:

select f.follower, f.following, f.type from followers f where f.following = :id order by f.date desc limit 0,25

How can I combine these so that if it selects from the followers table, it only takes the follower, following, and type and if it selects from the shares table, it only takes id, name, etc? Is this even possible?

Upvotes: 2

Views: 4933

Answers (1)

zevra0
zevra0

Reputation: 209

I think that you're looking for a union...

    select id, name, sharer, type from (        
    (select s.id as id, 
            s.name as name, 
            s.sharer as sharer, 
            s.type as type, 
            s.date as date 
       from shares s 
       where id = :id order by s.date desc)
        UNION
    (select f.follower as id, 
            f.following as name, 
            f.following as sharer,
            f.type as type,
            f.date as date
        from followers f 
        where f.following = :id order by f.date desc)) 
        order by date desc limit 0,25;

You may need to adjust the alias as needed.

Upvotes: 4

Related Questions