Results 1 to 4 of 4

Thread: sql query need help

  1. #1
    Join Date
    Aug 2003
    Posts
    7

    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

  2. #2
    Join Date
    Mar 2003
    Location
    Woking, UK
    Posts
    152
    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)

  3. #3
    Join Date
    Mar 2003
    Location
    Woking, UK
    Posts
    152

    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)

  4. #4
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    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
  •