CODEWITHSUNDEEP
mysqlsql
pal3
pal3

Reputation: 241

SQL show difference of two tables and compare the result to another table with different database

This is my dbtest1 structure

Database name: dbtest1

Table tbl1

id | name  | age
1  | johny | 26
2  | jane  | 25
3  | mike  | 32
4  | jean  | 33

Table tbl2

id | name | age
1  | john | 26
2  | jane | 25
3  | mike | 42

using this query below to get the difference of the two table.

SELECT *
FROM (
SELECT *, 'tableT1' AS fromTable FROM tbl1
UNION ALL
SELECT *, 'tableT2' AS fromTable FROM tbl2
) tbl
GROUP BY id, name, age
HAVING count(*) = 1
ORDER BY id;

I get this result.

id | name | age | fromTable
1  | john | 26  | tableT1
3  | mike | 42  | tableT2
4  | jean | 33  | tableT1

Now my problem is how can I able to compare the above result to dbtest2?I have no idea how to implement this..thanks in advance.

Database name: dbtest2

Table tbl3

id | name | age
1  | john | 26
2  | jane | 25
3  | mike | 42

Upvotes: 1

Views: 47

Answers (1)

Musa Haidari
Musa Haidari

Reputation: 2267

You can use fully qualified names. They are generally have the following syntax:

DATABASE_NAME.TABLE_NAME.COLUMN_NAME

Where each of the identifiers separated with . is the name of their namespaces. When you are issuing a query that is in current namespace, you can omit the name of the namespace, like you say desc tbl3 when you are in dbtest2.

Here is how you would do it in your query:

 SELECT *
 FROM (
     SELECT *, 'tableT1' AS fromTable FROM tbl1
     UNION ALL
     SELECT *, 'tableT2' AS fromTable FROM tbl2
     UNION ALL
     SELECT *, 'tableT3' AS fromTable FROM dbtest2.tbl3 # Here use fully qualified table name
 ) tbl
 GROUP BY id, name, age
 HAVING count(*) = 1
 ORDER BY id;

Upvotes: 1

Related Questions