excluding one query's results from another's? 1

[BeanDog]

I have a table that lists the UPCs that a user does not wish to see displayed. I also have a table that lists the UPCs' first 6 digits they do not want displayed, to exclude whole groups at once. Queries to list each one would be as follows:

SELECT * FROM tblExcludedUPC WHERE agentID=3
SELECT * FROM tblExcludedUPCGroup WHERE agentID=3

Another table lists those upcs. For instance,

SELECT * FROM tblItemByUPC

How would I build a query that would exclude the first queries from the latter one? Like:

SELECT * FROM tblItemByUPC
INNER NEGATIVE JOIN tblExcludedUPC.upc=tblItemByUPC.upc INNER NEGATIVE JOIN tblExcludedUPCGroup.upcprefix=LEFT(tblItemByUPC.upc, 6)
WHERE (tblExcludedUPC.agentID=3 AND tblExcludedUPCGroup.agentID=3)

Do you know what I mean? How would I pull this off? [sig][/sig]

 

[carp]

SELECT * FROM tblItemByUPC
MINUS
(SELECT * FROM tblExcludedUPC WHERE agentID=3
UNION ALL
SELECT * FROM tblExcludedUPCGroup WHERE agentID=3);

-or-

SELECT * FROM tblItemByUPC a
WHERE NOT EXISTS (SELECT 'x' FROM (SELECT your_code FROM tblExcludedUPC WHERE agentID=3
UNION ALL
SELECT your_code FROM tblExcludedUPCGroup WHERE agentID=3) b
WHERE a.your_code = b.your_code);

are a couple of approaches.

I'm willing to bet you'll get at least one or two more. [sig][/sig]

 

[BeanDog]

Here's the code I tried to use and got many errors:

SELECT ... FROM tblAd
...
WHERE NOT EXISTS (
SELECT * FROM dbo.tblExcludedUPCs WHERE dbo.tblExcludedUPCs.agentID=@pAgentID AND dbo.tblExcludedUPCs.excludedUPC=tblAd.upc
)
AND NOT EXISTS (
SELECT * FROM dbo.tblExcludedUPCGroups WHERE dbo.tblExcludedUPCGroups.agentID=@pAgentID AND dbo.tblExcludedUPCGroups.excludedupcgroup=LEFT(tblAd.upc,6)
)

What's wrong with this? And why can't I find any information on SQL syntax in MSDN? [sig][/sig]

 

[bigscouse]

I think it is the NOT EXISTS - FROM clause, you have: -
FROM dbo.tblExcludedUPCs

Try: -
FROM tblExcludedUPCs dbo [sig]<p>Ged Jones<br><a href= > </a><br>Top man[/sig]