Geek asks and answers

How to find out which table arose when using UNION in MySQL

I am using a join request:

SELECT snippet_id, title FROM tbl_snippets WHERE title LIKE ?
UNION
SELECT tag_id, tag FROM tbl_tags WHERE tag LIKE ?
UNION
SELECT category_id, category FROM tbl_categories WHERE category LIKE ?

How to find out which table the result came from? snippet_id, tag_id and category_id do not have a prefix, such as SN, TG or CI, which I could use to determine where the result came from. The results are basically combined into a single result, so I really don't know if what I think is why I asked here is possible.

+3
source share
1 answer

Why not add the prefix as a separate (calculated) column?

SELECT 'SN' prefix, snippet_id, title FROM tbl_snippets WHERE title LIKE ?
UNION ALL
SELECT 'TA', tag_id, tag FROM tbl_tags WHERE tag LIKE ?
UNION ALL
SELECT 'CA', category_id, category FROM tbl_categories WHERE category LIKE ?

Edit: I also changed UNION [DISTINCT]to UNION ALL- for the following reasons:

  • UNION UNION ALL, .
  • UNION ALL , UNION DISTINCT.
  • UNION ALL.
+4

More articles:


All Articles