Maybe there's another better way to do this... Anyway, here's what I'm trying to do:
I have two tables CarType & Cars. Table CarType has a column CarTypeId which is the primary key (int, identity). Table Cars includes a column CarTypeId (int) which is a foreign key into the CarType table.
Now I am trying to build a list of all those CarTypeId entries (from the CarType table) for which there are no Cars (i.e. there is no corresponding entry in the Cars table).
I read that there is an SQL "MINUS" keyword that you can use like this:
SELECT CarTypeId FROM CarType
MINUS
SELECT CarTypeId FROM Cars
So if CarType contains records with the keys {1, 2, 3, 4, 5} and Cars contains records with the foreign keys {2, 4} (using set notation), your result should be the set {1, 3, 5}. (Note: I have tried alternate versions of this where I selected the DISTINCT CarTypeId entries from Cars, and where I sorted them; results same in both cases).
I tried it in SQL Server 2005 Express. The result is just {1, 2, 3, 4, 5}.
My questions:
- Is the MINUS capability supported by SQL Server I know that there are all sorts of different SQL dialects & implementations and that SQL has evolved & transmutated over the years; perhaps they opted to leave this out of SQL Server...
- If not supported, well then I guess I have to throw myself at the feet of the real SQL gurus and ask if there's another way to accomplish this.
Here's a reference to the website where I initially found out about MINUS:
http://www.1keydata.com/sql/sql-minus.html
Thanks in advance for your time/comments.
Does SQL Server Support the "MINUS" Keyword?
Vikas Bindra
Caution: Microsoft talks about this issue at https://www.microsoft.com/technet/prodtechnol/sql/2000/reskit/part2/c0761.mspx mfr=true. However, it is incomplete in that it does not address a curveball.
That is, the = operator does not address nulls.
The Microsoft example is as follow:
SELECT CCODE, CNAME
FROM DEPT_ADMIN.CLASS
MINUS
SELECT C.CCODE, C.CNAME
FROM STUDENT_ADMIN.GRADE G,
DEPT_ADMIN.CLASS C
WHERE C.CCODE = G.CCODE
SELECT CCODE, CNAME
FROM DEPT_ADMIN.CLASSC
WHERE NOT EXISTS
(SELECT 'X' FROM
STUDENT_ADMIN.GRADE G
WHERE C.CCODE = G.CCODE)
This will work to identify students in one table but not in another. However, it will not work like Oracle's MINUS command to spot differences between tables. So, say you want to see this -- between the 2 tables, find where students are missing AND students whose names have changed -- either from A to B or from not null to null or from null to not null.
Try this:
SELECT CCODE, CNAME
FROM DEPT_ADMIN.CLASS C
WHERE NOT EXISTS
(SELECT 'X' FROM
STUDENT_ADMIN.GRADE G
WHERE C.CCODE = G.CCODE
and (C.CNAME = G.CNAME or (C.CNAME is null and G.NAME is null) )
That will mimic the full functionality of Oracle's MINUS command in SQL Server 2000 T-SQL.
Ken Culp
SELECT DISTINCT CarTypeId FROM CarType
WHERE (CarTypeId NOT IN
(SELECT CarTypeId FROM Cars)
)
the command DISTINCT goes in the first query, Because but it is like that, the query show the repeated rows....
sorry for my english because my language is Spanish , and even I do not do a course.... xD
bye... Regards from chili....
Max.
WN3335
I think you want EXCEPT
ms-help://MS.VSCC.v80/MS.VSIPCC.v80/MS.SQLSVR.v9.en/tsqlref9/html/b1019300-171a-4a1a-854f-e1e751de3565.htm
Stephanie Barulic
norax
SQL Server has never supported MINUS.
in SQL 2005 it now has the EXCEPT clause which does the same thing
Pimpom
Hi Ian,
I havent come across the Minus in SQL server but to do what you want I would try the following
SELECT T.cartypeid
FROM cartype as T
WHERE c.caretypeID is null
This joins everything in the cartype table but the where clause restricts the result set to those records with a null car.cartypeID
i.e. no matching record
Mo_Fya
Thanks Simon for your comments and pointing me in the right direction.
This is what worked for me:
SELECT CarTypeId FROM CarType
WHERE (CarTypeId NOT IN
(SELECT DISTINCT CarTypeId FROM Cars)
)
Still interested to know what became of the MINUS keyword though, because I think that syntax is slicker...
Ian