TSQL How to return results from two tables based on non matching rows

Question

I have two simple queries

SELECT Make, Color FROM (VALUES('Audi','Red'),('Audi','Blue'),('Audi','Black'),('BMW','Black'),('BMW','Yellow')) AS Cars(Make, Color)

SELECT Color FROM (VALUES('Red'),('Blue'),('Black'),('Yellow'),('White'),('Silver'),('Brown')) AS Colors(Color)

SQL1 Returns the Colors available from each Car Maker
SQL2 Returns the Colors of cars customers wishing to buy

I want to list the colors that are not available from each Car Maker.
eg. Audi White, Audi Yellow ...

Please help m with the TSQL query.

UPDATE: This is not a duplicate question. I have already tried that answer but I'm not getting the results I'm expecting. I wish people read the question before down voting or marking it as duplicate.

Possible duplicate of [How to select rows with no matching entry in another table?](https://stackoverflow.com/questions/4076098/how-to-select-rows-with-no-matching-entry-in-another-table) — Tab Alleman, Dec 20 '17 at 14:35
@Tab Alleman I have already looked at it, but I'm not getting the result I'm expecting. Please can you write the query I want using the method suggested in your link? — MSBI-Geek, Dec 20 '17 at 14:45

score 2 · Accepted Answer · answered Dec 20 '17 at 14:31

This get's you the answer you're after. As I put in the comments I assume you have a Makes table, however, as you haven't provided it, I haven't used it.

WITH C AS (
    SELECT Color FROM (VALUES('Red'),('Blue'),('Black'),('Yellow'),('White'),('Silver'),('Brown')) AS Colors(Color)),
MC AS (
    SELECT Make, Color FROM (VALUES('Audi','Red'),('Audi','Blue'),('Audi','Black'),('BMW','Black'),('BMW','Yellow')) AS Cars(Make, Color)),
--This is the answer
--I assume you actually have a table for Car makes, however, you haven't incldued that in your data, so enither have I:
M AS (
    SELECT DISTINCT Make
    FROM MC)
SELECT *
FROM C
     CROSS APPLY M
WHERE NOT EXISTS (SELECT *
                  FROM MC
                  WHERE C.Color = MC.Color
                    AND M.Make = MC.Make);

Thanks for the helpful answer – MSBI-Geek Dec 20 '17 at 14:43 — MSBI-Geek, Dec 20 '17 at 14:43

score 1 · Answer 2 · answered Dec 20 '17 at 14:57

Another option is to use EXCEPT

WITH C AS (
    SELECT Color FROM (VALUES('Red'),('Blue'),('Black'),('Yellow'),('White'),('Silver'),('Brown')) AS Colors(Color)),
MC AS (
    SELECT Make, Color FROM (VALUES('Audi','Red'),('Audi','Blue'),('Audi','Black'),('BMW','Black'),('BMW','Yellow')) AS Cars(Make, Color))

select Make, Color from C
cross join (select distinct Make from MC) as M
except
select Make, Color from MC

TSQL How to return results from two tables based on non matching rows

2 Answers2