asked on

Compare 2 tables to find unmatched records

I have 2 tables that don't necessary have the same number of fields. There is a logical reason why that is the case, but I don't think it is necessary here unless that is the reason this is not working for me.

In Table 1 (access_fund), the 2 fields that I am interested in comparing are: fund_number, fund_name. Table 2 (fund) have these 2 fields (fund_number, fund_name) as well.

I was able to executing this sql statement:

SELECT 'source_table' as tblaccess, a.fund_number, a.fund_name
FROM access_fund AS a
UNION ALL
SELECT 'destination_table' as tblaccess, b.fund_number, b.fund_name
FROM fund AS b

But when I tried to follow this example on how to compare the 2 tables to find unmatched records: http://www.mysqltutorial.org/compare-two-tables-to-find-unmatched-records-mysql.aspx

My code:

select min(tblaccess) as tblaccess, fund_number, fund_name
(
SELECT 'source_table' as tblaccess, a.fund_number, a.fund_name
FROM access_fund AS a
UNION ALL
SELECT 'destination_table' as tblaccess, b.fund_number, b.fund_name
FROM fund AS b
)
group by fund_number, fund_name
having count(*) = 1
order by fund_number

I get the following error:
SQL Error (1064): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT 'source_table' as tblaccess, a.fund_number, a.fund_name from access_fu' at line 3

I am using PHP 5.3 and mySQL.

Please let me know what syntax error am I violating.

cyberkiwi

That would be because you are missing a FROM before the subquery?

ASKER CERTIFIED SOLUTION

bigjdve

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

cyberkiwi

Interesting! Can't say I see any spaces in the query in the question after MIN nor COUNT.
If that were the case, the error message should have been
SQL Error (1630) Function <db>.min does not exist...

Anyway, glad you sorted it out.

stojanovicigi

Hi.
I think that you don't need UNION ALL, but JOIN statement.
Maybe I misunderstand your problem, but here's how I think the query should look like.
The query bellow will show you all records which exists in access_found and in found tables.
If you wan't to see all records which exists in foud, but don't exists in access_found table, just change where statement to " WHERE af.source_table IS NULL "

Hope this helped you.

SELECT *, af.source_table AS tblaccess, f.fund_number, f.fund_name FROM fund f
	LEFT JOIN access_fund af ON f.fund_number = af.fund_number
WHERE af.source_table IS NOT NULL
ORDER BY f.fund_number;

Open in new window

NerdsOfTech

use ` (slash quote? not sure what you call it) instead of ' (single quote) for field names

SELECT MIN(tbl_name) AS tbl_name, fund_number, fund_name
FROM
 (
  SELECT a.`source_table` as tbl_name, a.fund_number, a.fund_name
  FROM access_fund AS a
  UNION ALL
  SELECT b.`destination_table` as tbl_name, b.fund_number, b.fund_name
  FROM fund AS b
)  AS alias_table
GROUP BY fund_number, fund_name
HAVING COUNT(*)= 1
ORDER BY fund_number

Open in new window

NerdsOfTech

I like the UNION ALL subquery myself becuase you get the results in a 3 column set and with either table1 or table 2 as the source of the non-duplicated record.

Alternatively, a JOIN (cross join) can give you similar results to the UNION ALL subquery but you have 6 columns instead of 3.

SELECT a.`source_table`, a.fund_number AS a_fund_number, a.fund_name AS a_fund_name
FROM access_fund AS a
JOIN
SELECT b.`destination_table`, b.fund_number AS b_fund_number, b.fund_name AS b_fund_name
FROM fund AS b
ON a.fund_number = b.fund_number
order by a_fund_number

Open in new window

NerdsOfTech

quirk: in mySQL use ` (the tilde ~ key without shift AKA "back tick" character) instead of the single quote character ' to delimit the field names

ironically ' works in ms sql but apparently not in mySQL as a delimiter

nonetheless, since your fields don't have spaces just remove the delimiters altogether....

SELECT MIN(tbl_name) AS tbl_name, fund_number, fund_name
FROM
 (
  SELECT a.source_table as tbl_name, a.fund_number, a.fund_name
  FROM access_fund AS a
  UNION ALL
  SELECT b.destination_table as tbl_name, b.fund_number, b.fund_name
  FROM fund AS b
)  AS alias_table
GROUP BY fund_number, fund_name
HAVING COUNT(*)= 1
ORDER BY fund_number

Open in new window

bigjdve

ASKER

See my comment for solution.

bigjdve

ASKER

Found my own solution. Solution is presented.