My tables are below. I want to select all the items which are not associated with a particular quote. I tried this (which is totally wrong) SELECT * FROM item, quoteitem WHERE item.cat_ref not in (select * from quoteitem where quoteitem.quote_id = '""e_id&"')"Now I am totally confused about what I'm trying to select from where! Could somebody help me please? thanks :)CREATE TABLE Item ( cat_ref VARCHAR(5)PRIMARY KEY, descrip VARCHAR(50), date_added SMALLDATETIME, date_last_pricecheck SMALLDATETIME, cat_type VARCHAR(20), contract VARCHAR(10), cost_price SMALLMONEY, installation_charge SMALLMONEY, commercial_markup SMALLMONEY, supplier_name VARCHAR(20), supplier_phone VARCHAR(20),notes VARCHAR(500))CREATE TABLE QuoteItem ( quoted_price SMALLMONEY, quote_id INT FOREIGN KEY REFERENCES Quote(quote_id), cat_ref varchar(5) FOREIGN KEY REFERENCES Item(cat_ref) CONSTRAINT pk_QuoteItem PRIMARY KEY (quote_id, cat_ref) )CREATE TABLE Quote ( quote_id INT PRIMARY KEY, date_of_quote SMALLDATETIME, service_desk_contact VARCHAR(20), category INTEGER, first_name VARCHAR(10), surname VARCHAR(10), customer_phone VARCHAR(20), FOREIGN KEY (first_name, surname, customer_phone) REFERENCES Customer(first_name, surname, customer_phone)ON DELETE CASCADEON UPDATE CASCADE)CREATE TABLE Customer ( first_name VARCHAR(10), surname VARCHAR(10), customer_phone VARCHAR(20), contract VARCHAR(10), location VARCHAR(20), email VARCHAR(50), cust_id INT IDENTITY (1,1),PRIMARY KEY (first_name, surname, customer_phone))
query help please
Posted - 2004-08-25 : 05:43:45
