help with sql EXCEPT

hi all,

i'm obviously not looing at this command in the right way.
basically if i were to have the following tables

TypeID TypeNAME
1           type1
2           type2
3           type3
4           type4
5           type5

ProductID TypeID
2   3
2   4

so in this case product 2 has the types 3 and 4 associated with it i would like to pull out

TypeID Type
1 type1
2 type2
5 type5

i.e. all the types that are not associted with this product. i'm using the EXCEPT sql command but its not working. is this the correct command to be looking at or should i be doing it differently?

SELECT typeID FROM type EXCEPT SELECT typeID FROM productType WHERE productID=198 ORDER BY productType.productID;
flynnyAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
sujith80Connect With a Mentor Commented:
select * from type
where typeid not in (select typeid from producttype)
/

or by using exists clause

select * from type x
where NOT EXISTS (select 1 from producttype y where y.typeid = x.typeid)
/
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
please try this syntax:
select t.type 
from type t
left join productType pt
  on pt.productid = 198
 and pt.typeid = t.typeid
where pt.typeid is null

Open in new window

0
 
sonicefuCommented:

SELECT typeID FROM type 
MINUS 
SELECT typeID 
FROM productType 
WHERE productID=198 
ORDER BY productType.productID;

Open in new window

0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
sonicefuCommented:
EXCEPT is used in MySQL its equivalent in oracle is MINUS
0
 
mohammedfCommented:

SELECT typeID FROM type 
where ProductID  not in ( select TypeID  from table2)

Open in new window

0
 
sonicefuCommented:
* MINUS: returns all those records from the first query, which are not the in the second query
---------

Just to know about EXCEPT (equivalent to MINUS in oracle ), see the following links please.

http://en.wikipedia.org/wiki/Union_(SQL)#EXCEPT_operator

http://www.enterprisedb.com/documentation/sql-select.html#SQL-EXCEPT-MINUS

http://books.google.com/books?id=KFdPLkfGXsQC&pg=PA134&lpg=PA134&dq=sql+except+minus+comparison&source=web&ots=0drq2r66wX&sig=r4m2EXSBnEIQK03v2bVRcJUX-p4#PPA135,M1
--------------

Example of MINUS

http://www.java2s.com/Code/Oracle/Result-Set/AnMINUSquerythatusesanORDERBYclause.htm
---------------

<EXCEPT is used in MySQL its equivalent in oracle is MINUS> is not correct as i said in my last comment,
 i think EXCEPT is used in SAS, PostgreSQL and DB2 etc

SELECT   typeid
    FROM TYPE
MINUS
SELECT   typeid
    FROM producttype
   WHERE productid = 198
ORDER BY producttype.productid;

Open in new window

0
 
flynnyAuthor Commented:
great thanks for the links too.
0
All Courses

From novice to tech pro — start learning today.