Exclude like values with a JOIN?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Me! - 14 Feb 2008 11:34 GMT

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

Reply to this Message

zz - 14 Feb 2008 14:57 GMT

WHERE Yl_iD NOT IN (SELECT....)

-Dorian

> I have the following query:
>
[quoted text clipped - 13 lines]
>
> Many thanks.....Jason

Reply to this Message

Me! - 14 Feb 2008 16:25 GMT

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

> WHERE Yl_iD NOT IN (SELECT....)
>
[quoted text clipped - 17 lines]
>>
>> Many thanks.....Jason

Reply to this Message

John W. Vinson - 14 Feb 2008 18:56 GMT

>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)

Signature

John W. Vinson [MVP]

Reply to this Message

Me! - 20 Feb 2008 14:07 GMT

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

>>What I want to do is have my main query exclude all the Yl_iD values that
>>are in my secondary table.
[quoted text clipped - 11 lines]
> WHERE (dbo.qry_UV_CARS_PlateX_Valued.Yl_iD IS NULL)
> AND (secondarytable.Yl_iD IS NULL)

Reply to this Message

John W. Vinson - 20 Feb 2008 16:46 GMT

>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!"

Signature

John W. Vinson [MVP]

Reply to this Message