Check [A].[Col1] not exists in [B].[Col1] AND [B].[Col2]

  • Thread starter Thread starter ym2004
  • Start date Start date
Y

ym2004

Hi,

I am trying to write a query to select the rows if the values from a table
[A].[col1] is not found in another table .[col1] AND .[col2].

SELECT DISTINCT [oldINFO].[Reporter_Type] FROM [oldINFO]
WHERE [oldINFO].[Reporter_Type] NOt IN
( SELECT [English Description] FROM [tblk_Reporter Type] )

the above works for me but its not sufficient, I need to select for this
condition (below) but it failed cos Access does not allow the UNION for this
operation.

SELECT DISTINCT [oldINFO].[Reporter_Type] FROM [oldINFO]
WHERE [oldINFO].[Reporter_Type] NOt IN
( SELECT [English Description] FROM [tblk_Reporter Type]
UNION
SELECT [French Description] FROM [tblk_Reporter Type]
)

Any suggestions...thank you.
 
Found the solution .....


SELECT DISTINCT [oldINFO].[Reporter_Type] FROM [oldINFO]
WHERE [oldINFO].[Reporter_Type] NOt IN
( Select [Description] FROM
( SELECT [English Description] as [Description] FROM [tblk_Reporter Type]
UNION
SELECT [French Description] as [Description] FROM [tblk_Reporter Type] ) as
TEMP
)
 
Another option, might be...

SELECT ...
FROM OldInfo
WHERE NOT(ReporterType IN (SELECT [English Description] ...) OR
ReporterType IN (SELECT [French Description] ...))

SELECT DISTINCT [oldINFO].[Reporter_Type] FROM [oldINFO]
WHERE [oldINFO].[Reporter_Type] NOt IN
( SELECT [English Description] FROM [tblk_Reporter Type] )
Found the solution .....

SELECT DISTINCT [oldINFO].[Reporter_Type] FROM [oldINFO]
WHERE [oldINFO].[Reporter_Type] NOt IN
( Select [Description] FROM
( SELECT [English Description] as [Description] FROM [tblk_Reporter Type]
UNION
SELECT [French Description] as [Description] FROM [tblk_Reporter Type] ) as
TEMP
)

ym2004 said:
Hi,

I am trying to write a query to select the rows if the values from a table
[A].[col1] is not found in another table .[col1] AND .[col2].

SELECT DISTINCT [oldINFO].[Reporter_Type] FROM [oldINFO]
WHERE [oldINFO].[Reporter_Type] NOt IN
( SELECT [English Description] FROM [tblk_Reporter Type] )

the above works for me but its not sufficient, I need to select for this
condition (below) but it failed cos Access does not allow the UNION for this
operation.

SELECT DISTINCT [oldINFO].[Reporter_Type] FROM [oldINFO]
WHERE [oldINFO].[Reporter_Type] NOt IN
( SELECT [English Description] FROM [tblk_Reporter Type]
UNION
SELECT [French Description] FROM [tblk_Reporter Type]
)

Any suggestions...thank you.
Click to expand...
Click to expand...
 
Back
Top