Matthew Jarvis wrote:
> MB Software Solutions wrote:
>
>> I have a table called transactions. I want to do a query of all records
>> for a given criteria (account number), but I also want to specially note
>> those records where the pk doesn't exist in another table called
>> invoice_details. Basically, I need something like this (iid is the pk,
>> itransid is the fk):
>>
>> select * from invoice_details d1;
>> select t1.*, IFNULL('*',space(1)) as cBilled
>> from transactions t1
>> left join invoice_details d1
>> on d1.itransid = t1.iid
>> where t1.iacctid = 32
>>
>>
>> I tried the above SQL (btw, this is MySQL...substitute NVL for the
>> IFNULL for VFP), but the cBilled field still says '*' which it should
>> just have a space. E.g., There's a primary key in transactions of value
>> 1739. I did an insert into invoice_details where iacctid matched and
>> the itransid was 1739, hence that transaction line has been invoiced.
>>
>> What's wrong with my SQL logic? tia!
>> --Michael
>>
>>
>
>
> to get just a list of the pk's in Transactions but not in
> Invoice_Payments, wouldn't this do it (wrote something similar just this
> morning)?
>
> select pk from transactions tr where tr.pk not in ( select id.pk from
> invoice_payments id)
>
But I need the transaction data other than the keys for displaying in a
grid. I just wanted to accentuate/emphasize the debit rows that have
not yet been invoiced. I could take the sqlresult and SCAN loop it with
another query for each line doing a SELECT 1 from invoice_details where
itransid = <<sqlresult.itransid>> but I'd rather do it in ONE query
rather than keep going back and hitting the remote server for every row
in the first result set.
--
Michael J. Babcock, MCP
MB Software Solutions, LLC
http://mbsoftwaresolutions.com
http://fabmate.com
"Work smarter, not harder, with MBSS custom software solutions!"
_______________________________________________
Post Messages to: [email protected]
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/[EMAIL PROTECTED]
** All postings, unless explicitly stated otherwise, are the opinions of the
author, and do not constitute legal or medical advice. This statement is added
to the messages for those lawyers who are too stupid to see the obvious.
