"Tim Constantine" <sp**@togosolo.com> wrote in message
news:bb**************************@posting.google.c om...
I have a table "Subscriptions".
Subscriptions contains the columns "ChannelID" & "MemberID".
The "Channels" table contains a column called "Title"
When someone is viewing a channel, I would like to display "Members
who subscribe to this channel also subscribe to ..." and then list the
Titles of 3 or 4 channels ordered by the most popular.
I'm having trouble coming up with the correct MySQL syntax. Has
someone here done something like this before?
What do you mean by channel ? If I assume its something like a particular
magazine, then you want to select all the channels people who bought this
channel also bought (self join on your table), then order and choose.
Something like
select s2.ChannelID, count(*) c from subscriptions s1 join subscriptions s2
on s1.MemberID=s2.MemberID and s1.ChannelID!=s2.ChannelID where s1.ChannelID
= ?? group by s2.ChannelID order by c desc limit 5
Try that, its untested but should be a start.