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)
Or something like that?
Matthew S. Jarvis
IT Manager
Bike Friday - "Performance that Packs."
www.bikefriday.com
541/687-0487 x140
[EMAIL PROTECTED]
_______________________________________________
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.
