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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
sonicefuCommented:
EXCEPT is used in MySQL its equivalent in oracle is MINUS
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

mohammedfCommented:

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

Open in new window

0
SujithData ArchitectCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.