Not Common both Tables

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

This SQL selects all the common part numbers between the Main 2005/2006
tables but what criteria do I use to select the PN's that don't matchup (<>)
between the two tables?

Steve

SELECT Main2006.PartNumber, Main2006.AnnualCSIConfirmation,
Main2005.PartNumber, Main2005.AnnualCSIConfirmation
FROM Main2006
INNER JOIN Main2005 ON Main2006.PartNumber = Main2005.PartNumber
WHERE (((Main2006.AnnualCSIConfirmation)="Yes") AND
((Main2005.AnnualCSIConfirmation)="Yes"));
 
For Access or for SQL-Server?

You can use the Not Exists() statement with an Union:

Select Main2006.PartNumber, Main2006.AnnualCSIConfirmation
where Not Exists (Select * from Main2005 where Main2005.PartNumber =
Main2006.PartNumber Main2005.AnnualCSIConfirmation = "Yes")
and (Main2006.AnnualCSIConfirmation = "Yes")

Union All

Select Main2005.PartNumber, Main2005.AnnualCSIConfirmation
where Not Exists (Select * from Main2006 where Main2006.PartNumber =
Main2007.PartNumber and Main2006.AnnualCSIConfirmation = "Yes")
and (Main2005.AnnualCSIConfirmation = "Yes")

You can also use an Outer Join and select all the records which will have
their left or right values set to null; however, it's a little more
complicated to mix it with other WHERE clauses and I can't remember if the
Full Outer Join is supported by Access.
 
Thanks Sylain, Sorry for the late reply.

It's for MS-Access and I'm getting a syntax error (missing operator). I'll
keep looking. I also changed 2007 to 2005 in your post.

Steve
 
There is some differences between Access and SQL-Server for the syntax of
SQL. For exemple, I'm sure that the Full Outer Join is not available under
Access. However, I'm pretty sure that the Exits() statement and the UNION
are available but for the UNION statement, try removing the ALL operator.

For the syntax error, it's probably a missing AND in the first subquery of
the first Select:

.... Select Main2006.PartNumber, Main2006.AnnualCSIConfirmation
where Not Exists (Select * from Main2005 where Main2005.PartNumber =
Main2006.PartNumber AND Main2005.AnnualCSIConfirmation = "Yes")
and (Main2006.AnnualCSIConfirmation = "Yes") ...


Please note also that I'm not sure about the need for the filters like «
Main2005.AnnualCSIConfirmation = "Yes") » because I don't know what you
really need from your description of your problem. I've added them as an
exemple.
 
Sylvain,

This runs but it returns to many records in the query. I added the FROM


SELECT Main2006.PartNumber, Main2006.AnnualCSIConfirmation
FROM Main2006
WHERE Not Exists (SELECT * FROM Main2005 WHERE Main2005.PartNumber =
Main2006.PartNumber AND Main2005.AnnualCSIConfirmation = "Yes")
AND (Main2006.AnnualCSIConfirmation = "Yes")

UNION ALL

SELECT Main2005.PartNumber, Main2005.AnnualCSIConfirmation
FROM Main2005
WHERE Not Exists (SELECT * FROM Main2006 WHERE Main2006.PartNumber =
Main2005.PartNumber AND Main2006.AnnualCSIConfirmation = "Yes")
AND (Main2005.AnnualCSIConfirmation = "Yes");
 
Well, you must adapt the WHERE clauses with your proper need.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


Steve B. said:
Sylvain,

This runs but it returns to many records in the query. I added the FROM


SELECT Main2006.PartNumber, Main2006.AnnualCSIConfirmation
FROM Main2006
WHERE Not Exists (SELECT * FROM Main2005 WHERE Main2005.PartNumber =
Main2006.PartNumber AND Main2005.AnnualCSIConfirmation = "Yes")
AND (Main2006.AnnualCSIConfirmation = "Yes")

UNION ALL

SELECT Main2005.PartNumber, Main2005.AnnualCSIConfirmation
FROM Main2005
WHERE Not Exists (SELECT * FROM Main2006 WHERE Main2006.PartNumber =
Main2005.PartNumber AND Main2006.AnnualCSIConfirmation = "Yes")
AND (Main2005.AnnualCSIConfirmation = "Yes");

Sylvain Lafontaine said:
There is some differences between Access and SQL-Server for the syntax of
SQL. For exemple, I'm sure that the Full Outer Join is not available
under
Access. However, I'm pretty sure that the Exits() statement and the
UNION
are available but for the UNION statement, try removing the ALL operator.

For the syntax error, it's probably a missing AND in the first subquery
of
the first Select:

.... Select Main2006.PartNumber, Main2006.AnnualCSIConfirmation
where Not Exists (Select * from Main2005 where Main2005.PartNumber =
Main2006.PartNumber AND Main2005.AnnualCSIConfirmation = "Yes")
and (Main2006.AnnualCSIConfirmation = "Yes") ...


Please note also that I'm not sure about the need for the filters like «
Main2005.AnnualCSIConfirmation = "Yes") » because I don't know what you
really need from your description of your problem. I've added them as an
exemple.
Click to expand...
Click to expand...
 
Back
Top