Re: FN-FORUM: sql problem

date posted 26th December 2006 17:52

It is ok - solved it - I need a "group by" and a a select distinct - Pam

----- Original Message -----
From: [EMAIL REMOVED] [EMAIL REMOVED]
To: [EMAIL REMOVED]
Sent: Tuesday, December 26, 2006 5:54 PM
Subject: FN-FORUM: sql problem


>
> I have this bit of sql
>
>
>
>
> select members.memberid, username, artist_type, members.art_name, gender,
> dob, art_profile.country, genre1, genre2, genre3, countryname, art_city,
> town, profile_views, lastupdated, dformed, sum(music_tracks.totalplays) as
> total_plays, sum(totaldownloads) as total_downloads from (((((members left
> join art_profile on members.memberid = art_profile.memid) left join
> country
> on members.country = country.countryid) left join art_settings on
> members.memberid = art_settings.memid) left join music_tracks on
> music_tracks.memberid = members.memberid) inner join photo on
> members.memberid = photo.memberid) where memtypeid = 2 and ucreated = 1
> and
> directory_view = 'yes' group by members.memberid, username, artist_type,
> members.art_name, gender, dob, art_profile.country, genre1, genre2,
> genre3,
> countryname, art_city, town, profile_views, lastupdated, dformed,
> members.datejoined, paidmember order by sum(music_tracks.totalplays) DESC
>
>
>
>
> The problem is this bit
> inner join photo on members.memberid = photo.memberid
>
> The can be more than one photo. Infact there is 12 for one of them which
> is
> causing "sum(music_tracks.totalplays) as total_plays" to be 12 times the
> actual amount. How do I change the sql so that sum(music_tracks.totalplays
> does now get multiplied by the number of photos?
>
>
>
>
>
>
>
>
>
>
> Thanks
> Pam
>
>
>
>
>
> --
> Freelancers, contractors earn more with Prosperity4
> Call 0870 870 4414 or visit www.prosperity4.com
> and benefit from Inland Revenue approved expenses today.
>
> To advertise here: http://www.freelancers.net/advertising.html
>
>