CODEWITHSUNDEEP
mysqlsql
jose sanchez
jose sanchez

Reputation: 339

Using UNION, JOIN and ORDER by to Merge 2 identical tables

I need to join 2 identical tables to display the same list sorted by id. (posts and posts2)

It happens that before only worked with 1 table, but we've been using a second table (posts2) to store the new data from a certain id.

This is the query I used when I worked with 1 table(posts) and works fine.

select posts.id_usu,posts.id_cat,posts.titulo,posts.html,posts.slug,posts.fecha,hits.id,hits.hits,usuarios.id,usuarios.usuario,posts.id 
From posts 
Join hits On posts.id = hits.id
Join usuarios On posts.id_usu = usuarios.id 
where posts.id_cat='".$catid."' order by posts.id desc

Now I tried to apply this query to Union 2 tables, but I don't know at what point instantiate the JOINS. I tried several ways but sends MYSQL Error. The following query merge the 2 tables and order by id, but need to add the JOIN.

select * from (
    SELECT posts.id,posts.id_usu,posts.id_cat,posts.titulo,posts.html,posts.slug,posts.fecha 
        FROM posts where id_cat='6' ORDER BY id
    )X
UNION ALL
    SELECT posts2.id,posts2.id_usu,posts2.id_cat,posts2.titulo,posts2.html,posts2.slug,posts2.fecha         FROM posts2 where id_cat='4' ORDER BY id DESC limit 20

I need to add this at the above query

Join hits On posts.id = hits.id
Join usuarios On posts.id_usu = usuarios.id

Thanks in advance guys.

Upvotes: 1

Views: 58

Answers (1)

M Khalid Junaid
M Khalid Junaid

Reputation: 64476

If you want the same query as your first query but this time with union of your identical table i.e post2 then you can do so

select
p.id_usu,p.id_cat,p.titulo,p.html,p.slug,p.fecha 
,hits.id,hits.hits,usuarios.id,usuarios.usuario
from (
(select 
id_usu,id_cat,titulo,html,slug,fecha ,id
From posts
where id_cat='".$catid."' order by id desc limit 20)

UNION ALL

(select 
id_usu,id_cat,titulo,html,slug,fecha ,id
From posts2
where id_cat='".$catid."' order by id desc limit 20)
) p
Join hits On p.id = hits.id
Join usuarios On p.id_usu = usuarios.id 
order by p.id desc limit 20

Upvotes: 2

Related Questions