Show only those records that are not present in th

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Show only those records that are not present in th

Author

Topic

Berduchwal
Starting Member

3 Posts

Posted - 2007-03-19 : 05:22:25

I wanted to create a query that will search the table for all non "OK" values:

SELECT Checks.[GDay], Checks.[RCheck] AS [Error Type], "R" AS Contract
FROM Checks 
WHERE (((Checks.[GDay])>=#10/1/2006#) AND ((Checks.[RCheck])<>"OK"))

This part is fine but then I need to check if this error was already explained or it is new.

SELECT ErrorTable.[GDay], ErrorTable.[Errortype] AS [Error Type], "RCheck registered" AS Contract
FROM ErrorTable
WHERE (((ErrorTable.[GDay])>=#10/1/2006#) AND ((ErrorTable.[Contract])="R") AND ((ErrorTable.[Condition])<>"resolved"))

The last part would be to list only errors that have not been already explained. So effectively if one of the results of query 1 is also appearing as a result of query 2 it should not be listed. I do not know how to do this. Can anyone help?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-19 : 05:52:45

[code]SELECT GDay,
[Error Type],
Contract
FROM (
SELECT GDay,
RCheck AS [Error Type],
"R" AS Contract
FROM Checks
WHERE GDay >= #10/1/2006#
AND RCheck <> "OK"
) AS x
LEFT JOIN (
SELECT GDay,
Errortype AS [Error Type],
"RCheck registered" AS Contract
FROM ErrorTable
WHERE GDay >= #10/1/2006#
AND Contract = "R"
AND Condition <> "resolved"
) AS y ON y.GDay = x.GDay AND y.[Error Type] = x.[Error Type]
WHERE y.GDay IS NULL[/code]

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources