How do I do an inverse join with more than one key column?
In this baby-toy SqlServer example, I have the following
CREATE TABLE [dbo].[CarList](
[myID] [int] IDENTITY(1,1) NOT NULL,
[CarColour] [varchar](32) NOT NULL,
[CarName] [varchar](128) NOT NULL,
[CarCompany] [varchar](32) NOT NULL,
CONSTRAINT [PK_CarList] PRIMARY KEY CLUSTERED(
[myID] ASC,
[CarColour] ASC,
[CarName] ASC,
[CarCompany] ASC
)
)
GO
INSERT INTO CarList (CarColour, CarName, CarCompany)
VALUES('blue', 'Abe', 'Ford')
Elsewhere in the DB I have a table like
CREATE TABLE [dbo].[NewCars](
[CarColour] [varchar](32) NOT NULL,
[CarName] [varchar](128) NOT NULL,
[CarCompany] [varchar](32) NOT NULL,
)
GO
INSERT INTO NewCars (CarColour, CarName, CarCompany)
SELECT 'blue', 'Abe', 'Ford'
UNION ALL
SELECT 'blue', 'Abe', 'GM'
UNION ALL
SELECT 'blue', 'Betty', 'Ford'
UNION ALL
SELECT 'green', 'Abe', 'Honda'
Now I want to insert cars I don't already have in the CarList table
Something like...
INSERT INTO CarList ( CarColour, CarName, CarCompany)
SELECT DISTINCT new.CarColour, new.CarName, new.CarCompany
FROM NewCars new, CarList old
WHERE new.CarColour <> old.CarColour
AND new.CarName <> old.CarName
AND new.CarCompany <> old.CarCompany
Which doesn't work because the "blue', 'Betty', 'Ford' row will get filtered out...
If this were just a single ID of some kind it would be really easy
INSERT INTO myTable (myID, param1, param2, etc)
SELECT param1, param2, etc
FROM someOtherTable new
WHERE new.myID NOT IN (SELECT myID FROM myTable)
For reasons I don't really want to get into, I cannot remove rows from NewCars that match CarList. I also need to do this in one pass if possible.
[edit] Thanks guys!