how to combine these queries

Question

and get meaningful results.

Currently I am running these three queries:

  SELECT t.type,t.id,s.title FROM db1.tags t INNER JOIN db1.st s ON s.id=t.id WHERE id LIKE '%%' AND t.tag='foo' AND t.type='s' ORDER BY tag desc LIMIT 0, 19
  SELECT t.type,t.id,v.title FROM db1.tags t INNER JOIN db1.vi v ON v.id=t.id WHERE id LIKE '%%' AND t.tag='foo' AND t.type='v' ORDER BY tag desc LIMIT 0, 19
  SELECT t.type,t.id,i.ca AS title FROM db1.tags t INNER JOIN db2.tablename i ON i.id=t.id WHERE id LIKE '%%' AND t.tag='foo' AND t.type='i' ORDER BY tag desc LIMIT 0, 19

then trying to combine the data results but what I would really prefer is if I could combine them into a single query.

Any thoughts?

Answer 1

You can use UNION ALL:

SELECT * FROM (
    SELECT t.type,t.id,s.title
    FROM db1.tags t
    INNER JOIN db1.st s ON s.id=t.id
    WHERE id LIKE '%%' AND t.tag='foo' AND t.type='s'
    ORDER BY tag DESC
    LIMIT 0, 19
) AS T1
UNION ALL
SELECT * FROM (
    SELECT t.type,t.id,v.title
    FROM db1.tags t
    INNER JOIN db1.vi v ON v.id=t.id
    WHERE id LIKE '%%' AND t.tag='foo' AND t.type='v'
    ORDER BY tag DESC
    LIMIT 0, 19
) AS T2
UNION ALL
SELECT * FROM (
    SELECT t.type,t.id,i.ca AS title
    FROM db1.tags t
    INNER JOIN db2.tablename i ON i.id=t.id
    WHERE id LIKE '%%' AND t.tag='foo' AND t.type='i'
    ORDER BY tag DESC
    LIMIT 0, 19
) AS T3
ORDER BY type, id DESC

Answer 2

you should consider using UNION. since you always return the 3 same fields type/id/title

this would also make 3 internal requests to the database , however, you will be able to limit the results and order the full aggregated result.

Answer 3

If you don't want to use Unions you could try using outer joins. Add the t.type=something in the on clause. Finally you'd have to use a case statement to select the title. Not really easier than the Union approach but it might be faster since it would just be one call.