Query like Amazon's "also purchased..."

[Tim Constantine]

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?

[sks]

"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.

[Bill Karwin]

Tim Constantine wrote:

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.

SELECT COUNT(S.MemberID) AS MemberCount, C.Title
FROM Subscriptions S INNER JOIN Channels C ON S.ChannelID = C.ChannelID
INNER JOIN Subscriptions M ON S.MemberID = M.MemberID
WHERE M.ChannelID = ?
GROUP BY S.MemberID
ORDER BY MemberCount DESC
LIMIT 4

Substitute the currently viewed channel ID for the '?' parameter in that
query, and I think that'll work. I tried it out with a set of tables in
the app I'm working on, where my tables have similar relationships to
one another as your tables.

Regards,
Bill K.