Customers who bought this item also bought...

Customers who bought this item also bought....by now you probably know that I am talking about Amazon.com
So let's try to implement our own (simple) Amazon system
We will call it People who bought this song also bought these songs
So basically we will create 3 tables (ArtistTable, Songtable and SongPaid)
These are very basic tables and they are missing a lot of info, I created them so that the query is nice and simple
So if person A buys song 3 then we want to return all the songs (except song 3) from customers who also bought song 3 (except customer A)
we we also list how many times a song was bought, so that we can see what the most popular songs were that people bought who also bought the same song that we bought
Let's create our tables first

CREATE TABLE ArtistTable (ID INT PRIMARY KEY, ArtistName VARCHAR(500))
INSERT INTO ArtistTable VALUES(1,'Red Hot Chilli Peppers')
INSERT INTO ArtistTable VALUES(2,'Pearl Jam')
INSERT INTO ArtistTable VALUES(3,'Astral Projection')
INSERT INTO ArtistTable VALUES(4,'Chemical Brothers')

CREATE TABLE Songtable (ID INT PRIMARY KEY , SongName VARCHAR(500),ArtistID INT)
INSERT INTO Songtable VALUES(1,'Under The Bridge',1)
INSERT INTO Songtable VALUES(2,'Black',2)
INSERT INTO Songtable VALUES(3,'Mahadeva',3)
INSERT INTO Songtable VALUES(4,'Block Rockin Beats',4)
INSERT INTO Songtable VALUES(5,'Setting Sun',4)
INSERT INTO Songtable VALUES(6,'Alive',1)
INSERT INTO Songtable VALUES(7,'Give It Away Now',2)

CREATE TABLE SongPaid (SongID INT, CustID INT)
INSERT INTO SongPaid VALUES(1, 1)
INSERT INTO SongPaid VALUES(2, 1)
INSERT INTO SongPaid VALUES(1, 2)
INSERT INTO SongPaid VALUES(3, 3) -- Current Buyer (3)
INSERT INTO SongPaid VALUES(3, 4) -- Another Buyer (4)
INSERT INTO SongPaid VALUES(4, 4) -- Also bought by 4 ('Block Rockin Beats')
INSERT INTO SongPaid VALUES(5, 4) -- Also bought by 4 ('Setting Sun')
INSERT INTO SongPaid VALUES(4, 5)
INSERT INTO SongPaid VALUES(4, 6)
INSERT INTO SongPaid VALUES(5, 6)
INSERT INTO SongPaid VALUES(3, 7) -- Another Buyer (4)
INSERT INTO SongPaid VALUES(7, 7) -- Also bought by 7 ('Give It Away Now')
INSERT INTO SongPaid VALUES(4, 7) -- Also bought by 7 ('Block Rockin Beats')

CREATE INDEX ix_SongPaid ON SongPaid(SongID,CustID)

/*Now let's do the query with person 3 and song 3
our result set will be

SongID,SongName,ArtisName,SongCount
4,Block Rockin Beats,Chemical Brothers,2
5,Setting Sun Chemical Brothers,1
7,Give It Away Now,Pearl Jam,1

*/

DECLARE @SongId INT, @CustId INT
SELECT @SongId = 3,@CustId =3


SELECT s1.SongID ,st.Songname,a.ArtistName, Count(*) AS SongCount
FROM SongPaid s1
JOIN Songtable st on s1.SongID = st.ID
JOIN ArtistTable a on st.ArtistID = a.ID
WHERE EXISTS (
SELECT custid FROM SongPaid
WHERE SongID =@SongId AND CustID =s1.CustID )
AND SongID <> @SongId --Don't return the song we bought
AND CustID <> @CustId --Skip other songs we bought by skipping our customerid
GROUP BY s1.SongID ,st.Songname,a.ArtistName


The songs and bands I used in the tables are real so in case you don't know them here is some additional info with an Amazon link to the CD
'Under The Bridge' and 'Give It Away Now' are from the CD Blood Sugar Sex Magik by the Red Hot Chilli Peppers
'Black' and 'Alive' are from the CD Ten by Pearl Jam
'Mahadeva' is from the CD In The Mix by Astral Projection
'Setting Sun' and 'Block Rockin Beats'is from the CD Singles 93-03 by the Chemical Brothers