SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 query help please
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

newbie sql
Starting Member

12 Posts

Posted - 08/25/2004 :  05:43:45  Show Profile
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 CASCADE
ON 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))

spirit1
Cybernetic Yak Master

Slovenia
11751 Posts

Posted - 08/25/2004 :  05:55:56  Show Profile  Visit spirit1's Homepage
will this do?

select I.*
from Item I left join QuoteItem QI on (I.cat_ref = QI.cat_ref)
where QI.cat_ref is null

Go with the flow & have fun! Else fight the flow :)
Go to Top of Page

newbie sql
Starting Member

12 Posts

Posted - 08/25/2004 :  06:07:28  Show Profile
thats perfect thanks
Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.19 seconds. Powered By: Snitz Forums 2000