List:General Discussion« Previous MessageNext Message »
From:Don Wieland Date:March 2 2012 3:56am
Subject:Multi select Query help...
View as plain text  
Appreciate a little guidance here:

Background: I have an invoicing system. Invoices are generated and  
(invoice and Invoice Items) and Payments are generated (Payments and  
Payment Items). Payment items are amount of the Payment Total  
allocated to payoff open invoices. So I may have 3 open invoice for  
$100 each and I may generate one payment for $300 with 3 payment items  
for $100 each to pay off those 3 open invoices.

In most cases, clients will pay their own invoices off, but in rare  
cases another client will pay an invoice for that client (ie...  
spouse, parent, etc...) My client want me to some how display when the  
payee (or one of the payees - there can be multiple) of the invoice IS  
NOT the same client as the invoice being paid.

So I need to display a result that show a comma delineated string of  
payees OMITTING the invoice's client_id. I only want to show a result  
if one or more of the payees are different than the invoice's client_id.

So now with the mySQL queries that are working:

First of all, the client_id of the invoice I am querying on is 251719.

query 1 = select group_concat(payment_id) from tl_trans_pmt_items  
where inv_id = 1033911

This produces a string "1033882,1021630,1021632". These are parent  
Payment records which have the payee client_ids.

So if I run a query:

query 2 = select group_concat(client_id) FROM tl_transactions WHERE  
transaction_id IN (1033882,1021630,1021632)  AND client_id != 251719

This produces a string "251711,251713". These are the client_ids of  
the Payment records OMITTING the Invoice's client_id

So far this works fine. Now where I run into issues is where I try to  
combine these queries together:

query 3 = select group_concat(client_id) FROM tl_transactions WHERE  
transaction_id IN ((select group_concat(payment_id) from  
tl_trans_pmt_items where inv_id = 1033911))  AND client_id != 251719

I do not get the same results. Am I missing something? Hopefully  
something simple ;-)

Don




Thread
Multi select Query help...Don Wieland2 Mar
  • Re: Multi select Query help...hsv3 Mar