SQL Query to retrieve data while excluding a set of rows

Question

Hi All,

I have basically four tables (SQL Server):

Objects:
id
ObjectName

Components
id
ComponentName

ObjectsDetails:
ObjectID
ComponentID

ExclusionTable
id
ComponentID

Basically, these tables describe Objects and what Objects are made of (what components)

For example, Object "A" may be made out of component "A" and component "B".

In this case, the tables would be populated this way:

Objects:
id ObjectName
1 A

Components:
id ComponentName
1 A
2 B

ObjectDetails:
ObjectID ComponentID
1 1
1 2

Now, "ExclusionTable" may have a list of components that are to be excluded from a search (therefore, excluding entire objects if the object is made out of at least one of those components).

For example, I would like to ask:

"Give me all the Objects that are not made out of components A and B".

Therefore, my question is:

Is there a way to write a query for that ? No views, no stored procedures please.. my SQL engine does not support that.

I tried something like:

SELECT DISTINCT ObjectName FROM Objects INNER JOIN ObjectsDetails ON Objects.id =    
ObjectDetails.ObjectID WHERE ObjectsDetails.ComponentID NOT IN (1,2)

in case ExclusionTable tells us that Components A and B needs to be excluded.

Of course, that doesn't work...

I tried a few variations using WHERE NOT EXISTS (SELECT * FROM ExclusionTable) but I am not proficient enough in SQL to understand how to get it to work using one query only (if it is even possible).

Thanks!

Answer 1

Basically, if you need to get all objects not made from A or B, you need to get all objects EXCEPT those made from A or B.

SELECT DISTINCT Id, ObjectName 
FROM Objects 
WHERE Id NOT IN (
SELECT DISTINCT ObjectDetails.ObjectID 
FROM ObjectDetails
INNER JOIN Components ON ObjectDetails.ComponentID = Components.Id
WHERE Components.ComponentName = 'A' OR Components.ComponentName = 'B'
)

Would that be what you're looking for?

EDIT: Of course, you can omit the join if you already have the component ids - then just put those in the where clause to filter them out.

Answer 2

You should avoid doing queries with [not] in (select ...)

SELECT DISTINCT ObjectName 
FROM Objects 
INNER JOIN ObjectsDetails ON Objects.id = ObjectDetails.ObjectID 
LEFT JOIN ExclusionTable on ExclusionTable.ComponentId = ObjectsDetails.ComponentID
where ExclusionTable.ComponentId is null;

This will retrieve only rows for which the ComponentID is not in ExclusionTable.

Update:

SELECT ObjectName 
FROM Objects
INNER JOIN ObjectsDetails ON Objects.id = ObjectDetails.ObjectID 
LEFT JOIN ExclusionTable on ExclusionTable.ComponentId = ObjectsDetails.ComponentID
group by ObjectName 
having count(distinct ObjectsDetails.ComponentID) = sum(case when ExclusionTable.id is null then 1 else 0 end)

New approach, I think the only other way I could do it is basically to compare the number of components per object with the number of components in the object not included on the list. When these number are equal, no component is on the excluded list and we can show the object.

I'm sorry I can't make a test right now, please use EXPLAIN select ... to compare the queries, if they work.

Answer 3

select id, objectname from Objects left outer join ( select objectid from ObjectsDetails od inner join Exclusiontable et on od.ComponentID= et.ComponentID) excludedid on Objects.ID = excludedid.ObjectID and excludedid.ObjectID is null