CODEWITHSUNDEEP
mysqlunique
tsiger
tsiger

Reputation: 1647

mysql select unique records

I've got the following query:

select * from members,subscriptions
where members.MemberID = subscriptions.MemberID
and subscriptions.Year = 2009
and members.ASSCID = 15
and subscriptions.Untildate between '$2009-01-01' and '2009-12-31'
order by members.Memberlastname

Members pay either their annual subscription once (annual subscription) or they pay 2 times, one in january and one in june (six month subscriptions). What i want is to pull the members who paid at least once.

The statement above will bring some members twice (those who paid both on january and june).

Is is possible to pull the members who paid at least once (no matter if they paid an annual or a six month subscription). Avoiding duplicates.

Upvotes: 3

Views: 2243

Answers (3)

Adrian
Adrian

Reputation: 6099

All records from members table where each member has at least one subscription:

select members.*
from members join
(
   select
   members.MemberID, count(*)
   from members join subscriptions on (members.MemberID = subscriptions.MemberID)
   where subscriptions.Year = 2009
   and subscriptions.Untildate between '2009-01-01' and '2009-12-31'
   group by members.MemberID 
   having count(*) >= 1
)
v on ( members.MemberID = v.MemberID)
where members.ASSCID = 15

Upvotes: 1

Steve De Caux
Steve De Caux

Reputation: 1779

Edited Answer

You can do an exists on subscriptions to find the members who have paid at least once for a given year: 

select * from members
where  members.ASSCID = 15 and   
exists (select 1 from subscriptions  
where members.MemberID = subscriptions.MemberID   
and subscriptions.Year = 2009   

) 
order by members.Memberlastname

Upvotes: 2

Francis Upton IV
Francis Upton IV

Reputation: 19443

Use SELECT DISTINCT, that will get only the unique values of the columns you select.

Upvotes: 2

Related Questions