none
SQL SELECT

    Question

  • I am trying to select which clients invoices are sent to their invoice client (an alternate client who pays the bill) and whic are sent to the client himself. The client table has a client_number and an invoice_client_number.The invoice itself has a client number but it can be either invoice_client number or client number. THe only link to client from invoice is the client number. I thought that this would work...

    SELECT iv.invoice_id,iv.client_number FROM t_invoice iv
    join t_client c
    on iv.client_number = c.invoice_client_number
    and iv.client_number <> c.client_number
    and c.invoice_client_number <> c.client_number
    However, if a client has an invoice client,  that invoice client is also a client - in order to be an invoice client you must already be a client. So, the invoice client number can on one hand equal the invoice_client number yet it can also = the client number of a different client. Can this be resolved?

    Thursday, February 13, 2014 10:26 PM

Answers

  • Probably:

    SELECT iv.invoice_id,iv.client_number FROM t_invoice iv join t_client c on iv.client_number = c.invoice_client_number where not exists (select 1 from t_client C1

    where C1.invoice_client_number = c.client_number

    OR c1.client_number = c.invoice_client_number)



    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles



    Thursday, February 13, 2014 10:39 PM

All replies

  • Can you please post the DDL and sample DML so that it helps to solve the issue much faster...

    Looks like Parent Child query to me, but because of the Client, Invoice Client wordings I am getting lost in understanding...

    • Edited by sqlsaga Thursday, February 13, 2014 10:39 PM
    Thursday, February 13, 2014 10:34 PM
  • Probably:

    SELECT iv.invoice_id,iv.client_number FROM t_invoice iv join t_client c on iv.client_number = c.invoice_client_number where not exists (select 1 from t_client C1

    where C1.invoice_client_number = c.client_number

    OR c1.client_number = c.invoice_client_number)



    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles



    Thursday, February 13, 2014 10:39 PM
  • From what you say, there is either something missing in the data model - or there is something you have misunderstood.

    If the invoice only has the client that is to pay the invoice, it's a tad difficult to distinguish invoices a client pays on its own behalf or for someone else. Is there an order or similar which holds the originating client?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, February 13, 2014 10:52 PM
  • ELECT count(iv.invoice_id), eu.name FROM t_invoice iv
    join t_client c
    on iv.client_number <> c.invoice_client_number
    and iv.client_number = ( select client_number 
                             from t_client c1 
                             where c1.client_number = c.client_number)
    join t_partner_client pc
    ON pc.client_number = iv.client_number
    and pc.date_end > '2013-12-31'
    and pc.date_begin < '2013-12-31'
    join t_office_employee oe
    on  oe.employee_number = pc.employee_number
    and oe.date_end > '2013-12-31'
    and oe.date_begin < '2013-12-31'
    join t_office_EU ou
    on ou.office_number = oe.office_number 
    and ou.date_end > '2013-12-31'
    and ou.date_begin < '2013-12-31'
    join t_EU eu
    ON eu.EU_id = ou.EU_id
    where iv.date_invoice between '2012-01-01' and '2013-12-31'
    GROUP by eu.name 
    My final code - thank you
    Friday, February 14, 2014 2:33 PM