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.

LVL 3
bigjdveAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

cyberkiwiCommented:
That would be because you are missing a FROM before the subquery?
bigjdveAuthor Commented:
A typo.  The FROM is there.  And I figure it out.  I have extra spaces between MIN and tbl_name as well as spaces between COUNT and the (.  Once I have it corrected as follow:

SELECT MIN(tbl_name) AS tbl_name, fund_number, fund_name
FROM
 (
  SELECT 'source_table' as tbl_name, a.fund_number, a.fund_name
  FROM access_fund AS a
  UNION ALL
  SELECT '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

It is working now.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
cyberkiwiCommented:
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.
Become a CompTIA Certified Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

stojanovicigiCommented:
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

NerdsOfTechTechnology ScientistCommented:
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

NerdsOfTechTechnology ScientistCommented:
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

NerdsOfTechTechnology ScientistCommented:
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

bigjdveAuthor Commented:
See my comment for solution.
bigjdveAuthor Commented:
Found my own solution.  Solution is presented.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.