Identifying Source Table from Union Query

Identifying source table from UNION query

Should be easy enough, just do something like this:

SELECT Title, 1 FROM table1
UNION ALL
SELECT Title, 2 FROM table2
UNION ALL
SELECT Title, 3 FROM table3

Create a UNION query that identifies which table the unique data came from

If the CUSTOMER_ID appears in both tables then we'll have to arbitrarily pick which table to call the source. The following query uses "tblOne" as the [SourceTable] in that case:

SELECT
    CUSTOMER_ID,
    MIN(Source) AS SourceTable,
    COUNT(*) AS TableCount
FROM
    (
        SELECT DISTINCT
            CUSTOMER_ID, 
            "tblOne" AS Source
        FROM tblOne
    UNION ALL
        SELECT DISTINCT
            CUSTOMER_ID,
            "tblTwo" AS Source
        FROM tblTwo
    )
GROUP BY CUSTOMER_ID

How to return source table name when using a UNION?

Supply table names in a column along other columns you are pulling:

SELECT
  'table1' AS tablename,
  other columns
FROM table1

UNION ALL

SELECT
  'table2' AS tablename,
  other columns
FROM table2

UNION ALL

…

how to find which table contain record when using union in mysql

Include a discriminator column in the individual SELECT statements, with a distinct hardcoded value in each. As an example, we add a column named src_ to each query ...

SELECT '1' AS src_, foo FROM bar WHERE ...

 UNION ALL

SELECT '2' AS src_, grumblestilt FROM skin WHERE ...

---

We can use the value returned in that column to determine which SELECT returned a particular row.

Note: if we don't need to eliminate duplicate rows, we can use the UNION ALL set operator. The UNION operator will go through the work of identifying rows that are duplicates, and eliminating duplicates so we get a result set containing unique tuples.

Union of two tables but show which table the data came from

would this work?

SELECT 
    trans, MAX(max_amend) as max_max_amend
FROM
    (SELECT
        'a' AS src, trans, MAX(amend) AS max_amend
    FROM
        table_a
    GROUP BY
        trans

    UNION ALL

    SELECT
        'b' AS src, trans, MAX(amend) AS max_amend
    FROM
        table_b
    GROUP BY
        trans) m
GROUP BY
    trans

---

Lucero's point below is correct, the min(src) would be on the global set, not the related max()

I think you'd have to combine the source and table values into one column you can max. In your example, adding 1 to the value is all you need to distinguish the sources, like:

SELECT trans, Max(amend) AS MaxOfamend, 1+[amend] AS isa, 0 AS isb
FROM TableA
GROUP BY trans

but you could add 100, or multiply by a big value, or whatever works with your data. The idea is to combine the two pieces of information, the amend value and the source, into one column.

Then, after the information is combined, you get the max of that value, then strip off the source flag by uncombining them (subtracting 1, dividing by 100, whatever)

---

OK, here's what I got:

CREATE VIEW [dbo].[viewA]    AS
SELECT trans, MAX(amend + .20) AS srcIsA, 0 AS srcIsb
FROM  dbo.tableA
GROUP BY trans

CREATE VIEW [dbo].[viewB]    AS
SELECT trans, 0 AS srcIsA, MAX(amend + .10) AS srcIsB
FROM  dbo.tableB
GROUP BY trans

CREATE VIEW [dbo].[viewU]    AS
SELECT * from viewA
union all
select *
FROM  viewb

CREATE VIEW [dbo].[viewv]    AS
SELECT trans, srcIsA, srcIsb, srcIsA + srcIsb AS total
FROM  dbo.viewU

CREATE VIEW [dbo].[vieww]    AS
SELECT trans, MAX(total) AS max_total
FROM  dbo.viewv
GROUP BY trans

CREATE VIEW [dbo].[viewx] AS
SELECT trans, 
max_total, 
CAST(max_total AS int) AS maxval, 
CASE WHEN (max_total - CAST(max_total AS int)) = .1 THEN 'a' ELSE 'b' END AS src
FROM  dbo.vieww

How Can Identify Table in Mysql Union All

I would rewrite your query as with tablename that you want

SELECT 'comments' as table_name, col1, col2,... coln FROM comments c
WHERE user_id = '66' AND product_id = '3' 
AND status = 1  UNION ALL 
SELECT 'comments_reply' as table_name, col1, col2,... coln FROM comments_reply cr
WHERE user_id = '66' AND product_id = '3'
ORDER BY col DESC

And, i suspect if you have user_id, product_id has numeric type then you should use only values without single quote.

where user_id = 66 AND product_id = 3

SQL UNION - Adding Source

you can make use of an additional column col4 like this

select col1,col2,col3,sum(col4) 
from(    
    Select col1, col2, col3, 1 as col4 from table1
    UNION 
    Select col1,col2,col3, 2 as col4 from table4
)
group by col1,col2,col3

The records with col4=1 only exist in table1.

The records with col4=2 only exist in table2.

The records with col4=3 exist in both table1+table

How to know what table a result came from when using UNION in MySQL

Why not adding the prefix as a separate (computed) 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 have also changed UNION [DISTINCT] to UNION ALL - for the following reasons:

• If the original query produces different results for UNION and UNION ALL, introducing the prefix will change the number of result rows.
• In most cases UNION ALL is slightly faster than UNION DISTINCT.
• Most people actually want to have UNION ALL.

---

Related Topics