Excluding results from One to Many query

Question

I am having difficulty pulling data from a one-to-many table relationship. I need to pull a list of equipment-contracts that have not had the "Echo" warranty code applied to them yet. In my scenario I have an equipment-contracts table and a warranty table.

The design of equipment-contracts table (EQC hereafter) is:


------------------------------------------------------------------------------------
| EquipmentID  | Contract  | SerialNumber | Make | Model | Date         | Customer |
------------------------------------------------------------------------------------
| 001          | A1        | GDS12        | GRND | GR219 | 2016-03-02   | Acme Corp|
| 002          | B1        | BQWSD        | BQW  | BLU22 | 2016-03-10   | Fast Lawn|
| 003          | C1        | Foo36        | Foo  | Red18 | 2016-03-04   | Perfect T|
| 004          | D1        | Foo37        | Foo  | Red18 | 2016-03-04   | Perfect T|
| 005          | E1        | Foo38        | Foo  | Red18 | 2016-03-01   | Perfect T|
------------------------------------------------------------------------------------

The design of the warranty table (WAR hereafter) is:


---------------------------------------------------
| SerialNum | Make  | WarrantyCode | Warranty Date|
---------------------------------------------------
| GDS12     | GRND  | Alpha        | 2016-04-01   |
| GDS12     | GRND  | Bravo        | 2016-04-01   |
| GDS12     | GRND  | Delta        | 2016-04-01   |
| GDS12     | GRND  | Charlie      | 2016-04-01   |
| GDS12     | GRND  | Echo         | 2016-04-01   |
| BQWSD     | BQW   | Alpha        | 2016-04-01   |
| BQWSD     | BQW   | Bravo        | 2016-04-01   |
| BQWSD     | BQW   | Charlie      | 2016-04-01   |
| BQWSD     | BQW   | Foxtrot      | 2016-04-01   |
| BQWSD     | BQW   | Echo         | 2016-04-01   |
| Foo36     | Foo   | Alpha        | 2016-04-01   |
| Foo36     | Foo   | Bravo        | 2016-04-01   |
| Foo36     | Foo   | Charlie      | 2016-04-01   |
| Foo36     | Foo   | Hotel        | 2016-04-01   |
---------------------------------------------------

What I need to do is pull a list of EQC's that have not had the "Echo" warranty code applied to them yet. Which means that either they have no Echo record, or they have no warranty record period yet.

My result set should look like:


------------------------------------------------------------------------------------
| EquipmentID  | Contract  | SerialNumber | Make | Model | Date         | Customer |
------------------------------------------------------------------------------------
| 003          | C1        | Foo36        | Foo  | Red18 | 2016-03-04   | Perfect T|
| 004          | D1        | Foo37        | Foo  | Red18 | 2016-03-04   | Perfect T|
| 005          | E1        | Foo38        | Foo  | Red18 | 2016-03-01   | Perfect T|
------------------------------------------------------------------------------------

(GDS12 & BQWSD are exclueded because they have the Echo WarrantyCode applied. )

My query is:

SELECT EQC.EquipmentID
  ,EQC.Contract
  ,EQC.SerialNumber
  ,EQC.Make
  ,EQC.Model
  ,EQC.DATE
  ,EQC.Customer
FROM equipment - contracts EQC
LEFT JOIN warranty WAR
  ON WAR.SerialNum = EQC.SerialNumber
    AND WAR.Make = EQC.Make
WHERE WAR.WarrantyCode is null 
  OR WAR.WarrantyCode <> 'Echo'

The result set looks like this instead:


------------------------------------------------------------------------------------
| EquipmentID  | Contract  | SerialNumber | Make | Model | Date         | Customer |
------------------------------------------------------------------------------------
| 001          | A1        | GDS12        | GRND | GR219 | 2016-03-02   | Acme Corp|
| 001          | A1        | GDS12        | GRND | GR219 | 2016-03-02   | Acme Corp|
| 001          | A1        | GDS12        | GRND | GR219 | 2016-03-02   | Acme Corp|
| 001          | A1        | GDS12        | GRND | GR219 | 2016-03-02   | Acme Corp|
| 002          | B1        | BQWSD        | BQW  | BLU22 | 2016-03-10   | Fast Lawn|
| 002          | B1        | BQWSD        | BQW  | BLU22 | 2016-03-10   | Fast Lawn|
| 002          | B1        | BQWSD        | BQW  | BLU22 | 2016-03-10   | Fast Lawn|
| 002          | B1        | BQWSD        | BQW  | BLU22 | 2016-03-10   | Fast Lawn|
| 003          | C1        | Foo36        | Foo  | Red18 | 2016-03-04   | Perfect T|
| 003          | C1        | Foo36        | Foo  | Red18 | 2016-03-04   | Perfect T|
| 003          | C1        | Foo36        | Foo  | Red18 | 2016-03-04   | Perfect T|
| 003          | C1        | Foo36        | Foo  | Red18 | 2016-03-04   | Perfect T|
| 004          | D1        | Foo37        | Foo  | Red18 | 2016-03-04   | Perfect T|
| 005          | E1        | Foo38        | Foo  | Red18 | 2016-03-01   | Perfect T|
------------------------------------------------------------------------------------

Thorsten Kettner · Accepted Answer · 2016-04-04T21:06:40.730

4

You want EQCs for which not exists a Echo WAR. Or in other words: You want EQCs that are not in the Echo WAR set.

select *
from equipment eqc
where not exists
(
  select *
  from warranty war
  where war.serialnum = eqc.serialnumber
  and war.make = eqc.make
  and war.warrantycode = 'Echo'
);

Or:

select *
from equipment
where (serialnumber, make) not in
(
  select serialnum, make
  from warranty
  where warrantycode = 'Echo'
);

UPDATE: I am not sure, however, whether SQL Server supports tuples in IN clauses as shown above. It's a great feature which gets queries really readable as you can see, but some DBMS don't support this syntax unfortunately.

edited Apr 04 '16 at 21:06

answered Apr 04 '16 at 20:58

Thorsten Kettner

89,309
7
49
73

That only answers part of the question, you also have to factor in the date. OP asked "either they have no Echo record, or they have no warranty record period yet" – twoleggedhorse Apr 04 '16 at 20:59
@twoleggedhorse: I don't think so. Either there is an Echo warranty record or not. Or do you think we have to expect future dates in the warranty table? Then yes, we'd have to add a date comparision with today to the warranty where clauses. – Thorsten Kettner Apr 04 '16 at 21:03
Not very clear I know, but I think the implication is that there will be dates added in the future – twoleggedhorse Apr 04 '16 at 21:05
Sorry - i should have clarified. When I said "yet" i meant that eventually the record would be created. This will be used for a report, so date comparison will not be needed - I will simply run the report at a point in time and if the record doesn't exist than it should return within the result set. – ÁEDÁN Apr 04 '16 at 21:08
On the issue of tuples in SQL, the short answer is it is not currently supported but there are sort of workarounds (listed here: http://stackoverflow.com/questions/8006901/using-tuples-in-sql-in-clause) – twoleggedhorse Apr 04 '16 at 21:09

Excluding results from One to Many query

1 Answers1