none
help me break this up

    Question

  • I need to create a subquery. From what I understand is that a subquery is a query in a query with the inner most query executing 1st.

    Who are customers with no invoices placed. Display cus_lname, cus_fname, cus_balance.

    So I need to inner join the customer table with the invoice table by way of customer.cus_code = invoice.cus_code to see which customer has an invoice.

    So I want to break my code into 2 queries (because I learn better with a step by step process), then put them together. I'm just not sure what to seperate. Here is what I have:

    SELECT CUS_LNAME, CUS_FNAME
    FROM CUSTOMER 
    
    SELECT CUS_BALANCE
    FROM CUSTOMER INNER JOIN INVOICE ON CUSTOMER.CUS_CODE = INVOICE.CUS_CODE
    WHERE INV_NUMBER = CUSTOMER.CUS_BALANCE;

    Saturday, October 26, 2013 5:03 PM

Answers

  • Sub query may not be helpful in this, because you are trying to compare two columns.

    Why you want to remove and go for subquery?

    Also EXISTS is also can be a replacement.

    SELECT CUS_BALANCE, CUS_LNAME, CUS_FNAME
    FROM CUSTOMER 
    where CUS_BALANCE in ( select INV_NUMBER from INVOICE)
    and CUS_CODE  in ( select CUS_CODE from INVOICE)
    
    ---OR use exists 
    SELECT CUS_BALANCE, CUS_LNAME, CUS_FNAME
    FROM CUSTOMER CUSTOMER 
    where exists ( select * from  INVOICE WHERE CUSTOMER.CUS_CODE = INVOICE.CUS_CODE
     and INV_NUMBER = CUSTOMER.CUS_BALANCE)
    


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Saturday, October 26, 2013 5:36 PM

All replies

  • Sub query may not be helpful in this, because you are trying to compare two columns.

    Why you want to remove and go for subquery?

    Also EXISTS is also can be a replacement.

    SELECT CUS_BALANCE, CUS_LNAME, CUS_FNAME
    FROM CUSTOMER 
    where CUS_BALANCE in ( select INV_NUMBER from INVOICE)
    and CUS_CODE  in ( select CUS_CODE from INVOICE)
    
    ---OR use exists 
    SELECT CUS_BALANCE, CUS_LNAME, CUS_FNAME
    FROM CUSTOMER CUSTOMER 
    where exists ( select * from  INVOICE WHERE CUSTOMER.CUS_CODE = INVOICE.CUS_CODE
     and INV_NUMBER = CUSTOMER.CUS_BALANCE)
    


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Saturday, October 26, 2013 5:36 PM
  • I don't like doing people's homework.  Never did, never will. I'm a cranky 56 year old married to a teacher.

    Why do you think that you need to use an inner join?  Are there any other kinds of joins that might serve you better for your original question "Who are customers with no invoices placed?"  Do you need to do this with a subquery?  Is your assignment to use a subquery or is it to get the results of the second paragraph?  There is more than one way to solve this problem.

    Hope my questions open up the possibilities.


    Russel Loski, MCT, MCSE Data Platform/Business Intelligence. Twitter: @sqlmovers; blog: www.sqlmovers.com

    Saturday, October 26, 2013 5:39 PM
  • Who are customers with no invoices placed?     Display cus_lname, cus_fname, cus_balance.

    Try

    SELECT CUS_LNAME, CUS_FNAME, CUS_BALANCE
      from CUSTOMER as C
      where not exists (SELECT * from INVOICE as I where I.CUS_CODE = C.CUS_CODE);


        José Diz     Belo Horizonte, MG - Brasil
    Mark as answered if this post solved your problem and Vote as helpful if this post was useful.


    • Edited by Jose.Diz Sunday, October 27, 2013 11:58 PM
    Sunday, October 27, 2013 11:03 PM