CODEWITHSUNDEEP
sqlselect
Ant Smith
Ant Smith

Reputation: 91

Want to make my SELECT query simple when querying 2 or more tables with identical format

I have 2 identical tables that have the same format but populated with different data.

I want to do a SELECT query with group by and order by but do not want to use a UNION since it makes my query very long.

Here is a sample that works:

(SELECT a, b, c, d, e, f, g, MIN(h) as h, i 
 FROM `table1` 
 WHERE a LIKE '%this%' AND b LIKE '%that%' 
 GROUP BY b, a,c) 
UNION 
(SELECT a, b, c, d, e, f, g, MIN(h) as h, i 
 FROM `table2` 
 WHERE a LIKE '%this%' AND b LIKE '%that%' 
 GROUP BY b, a,c) 
ORDER BY 
    b DESC, h ASC, c ASC

Is there a more elegant way to make the query work?

Something like 

(SELECT a, b, c, d, e, f, g, MIN(h) as h, i 
 FROM `table1`,`table2` 
 WHERE a LIKE '%this%' AND b LIKE '%that%' 
 GROUP BY b, a, c) 
ORDER BY 
    b DESC, h ASC, c ASC`

Upvotes: 0

Views: 1049

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270463

What you want is union all in a subquery:

SELECT a, b, c, d, e, f, g, MIN(h) as h, i
FROM ((select table1.*
       from table1
      ) union all
      (select table2.*
       from table2
      )
     ) t
WHERE a LIKE '%this%' AND b LIKE '%that%'
GROUP BY b, a,c
order by b DESC, h ASC, c ASC

I assume the query is just representative, since you have e, f, g, i in the select with no aggregation functions.

Upvotes: 2

Related Questions