how to combine these queries
and get meaningful results.
Currently I am running these three queries:
SELEC开发者_如何学GoT 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?
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
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.
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.
精彩评论