Re: Not Common both Tables

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 Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


"Steve B." <SteveB@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:003C0EC3-E1A6-4E09-88C3-DA6563ADF023@xxxxxxxxxxxxxxxx
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

"Sylvain Lafontaine" wrote:

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.

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


"Steve B." <SteveB@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:0ABD9FD3-3C66-48AE-B0E9-938C96989C7A@xxxxxxxxxxxxxxxx
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"));





.

Relevant Pages

  • Re: Oracle outer join syntax (+) not working???
    ... Given the important advantages of the standard syntax (full ... Once I found out that FULL outer join is supported but a full ... isn't supported with the syntax, I switched to INNER JOIN, ...
    (comp.databases.oracle.misc)
  • Re: CROSS JOIN
    ... > was that depreciating the original FROM .. ... WHERE syntax would never ... > The problem is that the WHERE clause is done after the FROM clause. ... but thats just outer join stuff. ...
    (comp.databases)
  • Re: OUTER JOIN Problem
    ... Don't outer join. ... Use a union. ... select user, date, sumas incalls, 0 as saves, 0 as tickets ... > dtDate userID calls saves tickets ...
    (microsoft.public.sqlserver.programming)
  • Re: Difference in Left Join, Right Join
    ... RIGHT syntax exist. ... If you write SQL such that what you feel is the driving table is at ... The above is Oracle outer join syntax, ...
    (comp.databases.oracle.misc)
  • Re: SQLServer/Oracle Views
    ... >Mike John ... I suddenly remembered something Joe Celko ... It's not a syntax difference of the old outer-join syntax, ... LEFT OUTER JOIN TableB AS b ...
    (microsoft.public.sqlserver.programming)