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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
