Hardest Query of My Life.. :) (Help Please!)

I have a payment table that has the following (important) fields:
pmt_id (pk)
pmt_link_id (fk to an "account")
pmt_code
pmt_recovery (1 or 0)
The Account table has:
lcl_id (pk)
pmt_link_id (used as fk to payment)
I need to write a query that will tell me a list of Accounts that have a
recovery='1' (for a distinct pmt_code) that does NOT have a payment (where
recovery='0') for that account also...is that even possible?
Message posted via http://www.webservertalk.comTry this:
SELECT lcl_id
FROM Account AS A
JOIN Payment AS P
ON A.pmt_link_id = P.pmt_link_id
GROUP BY lcl_id
HAVING MIN(pmt_recovery)=1
If that's not it, please follow the advice in this article on how best
to post your problem here:
http://www.aspfaq.com/etiquette.asp?id=5006
David Portas
SQL Server MVP
--|||Try this
select a.lcl_id,a.pmt_link_id from Account as a
inner join payment as b on a.lcl_id=b.pmt_link_id and b.pmt_recovery=1
left join (select distinct pmt_link_id from payment where recovery=0) as c
on a.lcl_id=c.pmt_link_id
where c.pmt_link_id is NULL
Babu M K
Comat Technologies Pvt. Ltd
"Jon Jensen via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:08764836d2f54d06945788f5edb81020@.SQ
webservertalk.com...
> I have a payment table that has the following (important) fields:
> pmt_id (pk)
> pmt_link_id (fk to an "account")
> pmt_code
> pmt_recovery (1 or 0)
> The Account table has:
> lcl_id (pk)
> pmt_link_id (used as fk to payment)
> I need to write a query that will tell me a list of Accounts that have a
> recovery='1' (for a distinct pmt_code) that does NOT have a payment (where
> recovery='0') for that account also...is that even possible?
> --
> Message posted via http://www.webservertalk.com|||Minor change in the query
select a.lcl_id,a.pmt_link_id from Account as a
inner join payment as b on a.pmt_link_id=b.pmt_link_id and b.pmt_recovery=1
left join (select distinct pmt_link_id from payment where recovery=0) as c
on a.pmt_link_id=c.pmt_link_id
where c.pmt_link_id is NULL
Babu
Comat Technologies Pvt. Ltd
"Jon Jensen via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:08764836d2f54d06945788f5edb81020@.SQ
webservertalk.com...
> I have a payment table that has the following (important) fields:
> pmt_id (pk)
> pmt_link_id (fk to an "account")
> pmt_code
> pmt_recovery (1 or 0)
> The Account table has:
> lcl_id (pk)
> pmt_link_id (used as fk to payment)
> I need to write a query that will tell me a list of Accounts that have a
> recovery='1' (for a distinct pmt_code) that does NOT have a payment (where
> recovery='0') for that account also...is that even possible?
> --
> Message posted via http://www.webservertalk.com|||"Jon Jensen via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:08764836d2f54d06945788f5edb81020@.SQ
webservertalk.com...
>I have a payment table that has the following (important) fields:
> pmt_id (pk)
> pmt_link_id (fk to an "account")
> pmt_code
> pmt_recovery (1 or 0)
> The Account table has:
> lcl_id (pk)
> pmt_link_id (used as fk to payment)
> I need to write a query that will tell me a list of Accounts that have a
> recovery='1' (for a distinct pmt_code) that does NOT have a payment (where
> recovery='0') for that account also...is that even possible?
> --
> Message posted via http://www.webservertalk.com
If that's the "hardest query of your life", then
you've had a relatively easy life of writing
queries thus far <grin>.