people who liked x also liked y

[intomuso]

I'm trying to do a query which is way beyond my usual sql statements and I'm not sure what to use.

I have 3 tables

Songtable (fields - ID,Songname,ArtistID)
SongPaid (fields - SongID,CustID)
ArtistTable (fields - ID, ArtistName)

So what I want to do is look into the "SongPaid" table and see which customers bought that song. The table stucture

SongID CustID
246 312
231 312
133 312
133 345
23 312
23 412
677 412

then look at other customers who bought that track or artist and see what they bought

so for example the song 23 was bought by customers 312 and 412 - who also bought songID 246,231,133 and 617, which are by the artist, find that from songtable.artistid

I need some way of doing this comparison in the songpaid table.

 

[SQLBill]

What is the relationship between the tables? I see SongID and CustID in SongPaid table, but neither of those are in the other two tables. Or is ID in the other two tables the same as SongID?

-SQLBill

Posting advice: FAQ481-4875

 

[SQLDenis]

create table SongPaid (SongID int,   CustID int)
--test data
insert into SongPaid values(246,    312)
insert into SongPaid values(231,    312)
insert into SongPaid values(133,    312)
insert into SongPaid values(133,    345)
insert into SongPaid values(23,    312)
insert into SongPaid values(23,    412)
insert into SongPaid values(677,    412)

--here is the query
declare @SongId int
select @SongId = 23

select s1.SongID 
from SongPaid s1
where exists (
select custid from SongPaid
where SongID =@SongId and CustID =s1.CustID )
and SongID <> @SongId

rows returned
133
231
246
677 --not 617, typo?

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[intomuso]

Hi,

yes

The SongPaid.SongID - > SongTable.ID
ArtistTable.ID - > SongTable.ArtistID

Hope you can make something out of it

Thanks

gavin

 

[SQLDenis]

This should do it (famous last words....)

declare @SongId int
select @SongId = 23

select s1.SongID ,st.Songname,a.ArtistName
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

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[r937]

famous last words

select s1.SongID ,st.Songname,a.ArtistName
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 )


r937.com | rudy.ca

 

[SQLDenis]

Rudy isn't that incorrect?
If you add this to the table
insert into SongPaid values(666, 1)

this song should not be returned since customerID 1 did not buy song 23 but only song 666 (Sympathy for the devil)

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[r937]

no, wait, that's still not right...

r937.com | rudy.ca

 

[r937]

yes, i'm sorry, i was confused, and should have taken the time to test

r937.com | rudy.ca

 

[intomuso]

Hi,

Thanks - I hope the query Where Exists will work in mySQL 3.23
I think the basic ideas you've given can be adapted

declare @SongId int
select @SongId = 23

select s1.SongID ,st.Songname,a.ArtistName

I don't know if you can do that.

Thanks for your help I'll let you know if it works

 

[SQLBill]

Crap. You are using MySQL???? This forum is for Microsoft's SQL Server. You need to post your question in the MySQL forum.

-SQLBill

Posting advice: FAQ481-4875