mysql select unique records

Question

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.

score 2 · Answer 1 · answered Dec 15 '09 at 10:54

2

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

answered Dec 15 '09 at 10:54

Francis Upton IV

19,322
3
53
57

See this SO post "Is there any difference between Group By and Distinct" http://stackoverflow.com/questions/164319/is-there-any-difference-between-group-by-and-distinct – Adrian Dec 15 '09 at 10:57
@Adrian thanks for that, I had never thought about that before. – Francis Upton IV Dec 15 '09 at 11:06

Steve De Caux · Answer 2 · 2009-12-15T11:26:55.210

2

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

edited Dec 15 '09 at 11:26

answered Dec 15 '09 at 10:58

Steve De Caux

1,779
12
13

year is used to specify for what year a member is paying. the could be paying in 2007 for the year 2006 or 2005 – tsiger Dec 15 '09 at 11:05

Adrian · Accepted Answer · 2009-12-15T11:32:13.343

1

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

edited Dec 15 '09 at 11:32

answered Dec 15 '09 at 10:55

Adrian

6,013
10
47
68

Not sure about the "having count(*) >1" - wouldn't that exclude the people who paid only once? – Francis Upton IV Dec 15 '09 at 10:58
i am having a select * in my statement because i want to display the records in a table. Do i have to select each field seperately like members.MemberID? – tsiger Dec 15 '09 at 10:59
@Gerasimos You can either include all wanted fields from members table in the group by list or join back to members table on MemberID (primary key I assume) – Adrian Dec 15 '09 at 11:02
Consider also Steve De Caux's solution which uses "exists" - it might be more performant – Adrian Dec 15 '09 at 11:43

mysql select unique records

3 Answers3

Edited Answer