Exclude like values with a JOIN?

[Me!]

I have the following query:

SELECT dbo.qry_UV_CARS_PlateX_ShouldBeValued.Yl_iD
FROM dbo.qry_UV_CARS_PlateX_Valued RIGHT OUTER JOIN
dbo.qry_UV_CARS_PlateX_ShouldBeValued ON
dbo.qry_UV_CARS_PlateX_Valued.Yl_iD =
dbo.qry_UV_CARS_PlateX_ShouldBeValued.Yl_iD
WHERE (dbo.qry_UV_CARS_PlateX_Valued.Yl_iD IS NULL)

And I have another secondary table containing the same field as Yl_iD.

What I want to do is have my main query exclude all the Yl_iD values that
are in my secondary table.

What JOIN and criteria will do this?

Many thanks.....Jason

 

[zz]

WHERE Yl_iD NOT IN (SELECT....)

-Dorian

 

[Me!]

Thanks Dorian,

I've added the SELECT from the other query and it isn't working - I'm
getting an 'Incorrect syntax near keyword 'WHERE' ' error message. The full
SQL is:

SELECT DISTINCT dbo.CAR_RangeDescriptions.Rd_Description,
dbo.qry_UV_CARS_PlateX_ShouldBeValued.Yl_iD
FROM dbo.qry_UV_CARS_PlateX_ShouldBeValued INNER JOIN
dbo.CAR_YearLetter ON
dbo.qry_UV_CARS_PlateX_ShouldBeValued.Yl_iD = dbo.CAR_YearLetter.Yl_iD INNER
JOIN
dbo.CAR_RangeDescriptions ON
dbo.CAR_YearLetter.Yl_RangeName = dbo.CAR_RangeDescriptions.Rd_RangeName
LEFT OUTER JOIN
dbo.qry_UV_CARS_PlateX_Valued ON
dbo.qry_UV_CARS_PlateX_ShouldBeValued.Yl_iD =
dbo.qry_UV_CARS_PlateX_Valued.Yl_iD
WHERE (dbo.qry_UV_CARS_PlateX_Valued.Yl_iD IS NULL)
WHERE (Yl_iD NOT IN (SELECT DISTINCT dbo.CAR_YearLetter.Yl_iD AS CAPId,
dbo.CAR_RangeDescriptions.Rd_Description AS Range
FROM dbo.CAR_RangeDescriptions INNER
JOIN
dbo.CAR_YearLetter ON
dbo.CAR_RangeDescriptions.Rd_RangeName = dbo.CAR_YearLetter.Yl_RangeName
WHERE
(dbo.CAR_RangeDescriptions.Rd_Description LIKE 'Z NON%')))
ORDER BY dbo.CAR_RangeDescriptions.Rd_Description

Any help greatly appreciated. (By the way I'm in an Acces project hence the
T-sql syntax)...Regards, Jason

 

[John W. Vinson]

What I want to do is have my main query exclude all the Yl_iD values that
are in my secondary table.

A "Frustrated Outer Join" should do the trick:

SELECT dbo.qry_UV_CARS_PlateX_ShouldBeValued.Yl_iD
FROM (dbo.qry_UV_CARS_PlateX_Valued
RIGHT OUTER JOIN dbo.qry_UV_CARS_PlateX_ShouldBeValued
ON dbo.qry_UV_CARS_PlateX_Valued.Yl_iD =
dbo.qry_UV_CARS_PlateX_ShouldBeValued.Yl_iD)
LEFT OUTER JOIN secondarytable
ON secondarytable.Yl_iD = dbo.qry_UV_CARS_PlateX_Valued.Yl_iD
WHERE (dbo.qry_UV_CARS_PlateX_Valued.Yl_iD IS NULL)
AND (secondarytable.Yl_iD IS NULL)

 

[Me!]

Thanks John. Your help's appreciated. (and the solution seems obvious now -
LEFT JOIN and Null check to give me an EXCEPT operation....doh!)

Regards......Jason

 

[John W. Vinson]

Thanks John. Your help's appreciated. (and the solution seems obvious now -
LEFT JOIN and Null check to give me an EXCEPT operation....doh!)

Don't you wish the database software developers could get together on jargon!
You can do this kind of thing in any dialect of SQL... but the terminology is
different in all of them.

"God must love standards, She made so many of them!"