Results 1 to 3 of 3

Thread: Subquery

  1. #1
    Join Date
    Jan 2004
    Location
    California
    Posts
    10

    Unanswered: Subquery

    My Query:

    INSERT INTO Item_Billing_Link (Product_ID) SELECT Products.Product_ID FROM Products WHERE Products.Recurring = "yes" AND Products.Cust_ID = (SELECT Customers.Cust_ID FROM Customers WHERE Customers.Bill_Cycle_ID = "1");


    Problem:

    Failed to execute SQL : SQL INSERT INTO Item_Billing_Link (Product_ID) SELECT Products.Product_ID FROM Products WHERE Products.Recurring = "yes" AND Products.Cust_ID = (SELECT Customers.Cust_ID FROM Customers WHERE Customers.Bill_Cycle_ID = "1") failed : Subquery returns more than 1 row

    Help!

  2. #2
    Join Date
    Jan 2004
    Location
    Aberdeen
    Posts
    12
    Hi,

    I'd say the subquery it doesn't like is the

    AND Products.Cust_ID = (SELECT Customers.Cust_ID FROM Customers WHERE Customers.Bill_Cycle_ID = "1")

    bit. It's likely that this select returns more than one value so what you are saying is something like -

    AND Products.Cust_ID = (1,10,12,15,19,32,etc)

    You instead want to achieve something like

    AND Products.Cust_ID IN (1,10,12,etc)
    Calum
    www.4-3-3.com
    Online football Management Game

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    PHP Code:
    insert 
      into Item_Billing_Link 
    (Product_ID
    select distinct
           Products
    .Product_ID 
      from Products 
    inner
      join Customers  
        on Products
    .Cust_ID Customers.Cust_ID
     where Products
    .Recurring 'yes' 
       
    and Customers.Bill_Cycle_ID 
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •