-
sql query need help
hi,
i am having problem selecting the data i want..
i have 3 tables as follow :
Customer table
---------------------------
customerid customername
Product table
---------------------------
productid productname
Order product (Customer - Product) table
---------------------------
customerid productid
given a customer id, i wan to be able to select all products that the given customer had NOT yet ordered.... is this possible?? hope someone can help me... thz a lot
-
select * from Customer c ,Product p
where not exists (select * from Order_product where customerid = c.customerid and productid = p.productid)
--and customerid = 1
--and productid = 3
You Have To Be Happy With What You Have To Be Happy With (KC)
-
or
select * from Customer c join Product p on (1 = 1)
left join Order_product o on (o.customerid = c.customerid and o.productid = p.productid)
where (o.customerid is null or o.productid is null)
--and c.customerid = 1
--and p.productid = 3
You Have To Be Happy With What You Have To Be Happy With (KC)
-
use tempdb
go
create table Customer (customerid int, customername varchar(100))
go
create table Product (productid int,productname varchar(100))
go
create table Orderx (customerid int,productid int)
go
insert into customer select 1,'MAK'
insert into customer select 2,'John'
insert into customer select 3,'Mango'
go
Insert into Product select 1, 'Keyboard'
Insert into Product select 2, 'Lamp'
Insert into Product select 3, 'Wallpaper'
Insert into Product select 4, 'Telephone'
Insert into Product select 5, 'Bottle'
go
Insert into Orderx select 1,5
Insert into Orderx select 2,1
Insert into Orderx select 2,3
Insert into Orderx select 2,2
Insert into Orderx select 3,1
Insert into Orderx select 3,5
go
--for MAK
select ProductName from product where productid not in (
select productid from orderx where customerid= (select customerId from customer where customerName='MAK'))
or
--if you know customer ID
select ProductName from product where productid not in (
select productid from orderx where customerid= 1)
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|