Query Question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Query Question

Author

Topic

Lac0niC
Starting Member

5 Posts

Posted - 2007-06-14 : 08:20:05

Hi. I have three table. Payments, Members, MemberPayments

Payments Columns: PaymentID, Year (Payments are annual)

Members: MemberID, MembershipDate

MemberPayments: MemberID, PaymentID

MemberPayments hold only payments that is paid like:

User1 PaymentID1
User1 PaymentID2
User2 PaymentID1

I want to find payments that is not paid by users. For example user1's and user2's membership date year is 2006. User2 hasn't paid 2007 payment (PaymentID2) yet. I want a query that will find it but i'm not good at tsql. Do you help? Thanks in advance.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-06-14 : 08:25:16

[code]Select p.*
From Payments p LEFT JOIN MemberPayments mp
on mp.PaymentID = p.PaymentID
WHERE mp.PaymentID IS NULL[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Lac0niC
Starting Member

5 Posts

Posted - 2007-06-14 : 09:53:55

quote:

Originally posted by harsh_athalye

Select p.*
From Payments p LEFT JOIN MemberPayments mp 
on mp.PaymentID = p.PaymentID
WHERE mp.PaymentID IS NULL

Thank you Harch. This one only works for one user? How can i list all indepted users like that:

Username NotPaidYear
User2 PaymentID2
User3 PaymentID1
User3 PaymentID2

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-06-14 : 23:53:14

Select <col list> from members m
left join MemberPayments mp
on m.MemberID = mp.MemberID
left join Payments p
on mp.PaymentID = p.PaymentID
where mp.PaymentID IS NULL

--------------------------------------------------
S.Ahamed

Lac0niC
Starting Member

5 Posts

Posted - 2007-06-15 : 03:08:12

I will try to tell again:

insert into Payment values (11,2005)
insert into Payment values (12,2006)
insert into Payment values (13,2007)

-----------------

insert into Members values ( 1, 2005-01-13)
insert into Members values ( 2, 2006-01-13)
insert into Members values ( 3, 2006-01-13)

---------

insert into MemberPayments values (1,11)
insert into MemberPayments values (1,12)
insert into MemberPayments values (2,12)

I want to list years that is not paid like:

Member UnPaidYear

1 2007
2 2007
3 2006
3 2007

With words; for example u r a member since 2006 and just paid for 2006. I'm a member since 2007 and have no record in MemberPayments. (MemberPayments just hold payments that is paid). So i need a query that shows you didn't paid for 2007 and i didn't paid for 2007. Records that is not exist in MemberPayments due to MembershipDate.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources