[ad_1]

I have a SQL question.

Say I have three tables:

Table 1: Artist

id bandid name
1  1      Paul McCartney
2  1      Ringo Starr
3  1      John Lennon
4  1      George Harrison
5  2      Mick Jagger
6  2      Keith Richards
7  2      Charlie Watts
8  2      Ronnie Wood
9  3      Travis Barker
10 3      Tom Delonge
11 3      Mark Hoppus

Table 2: Band

bandid bandname
1      the beatles
2      rolling stones
3      blink 182

Table 3: Artist on Album

albumid artistid
1       1
1       2
1       3
1       4
2       1
2       2
2       3
2       4
3       5
3       6
3       7

I’m seeking to count the number of albums two artists have worked together on, listing artist #1 and artist #2 names, the band they are both in, and the number of albums between them, while using an subquery. Another note: listing artist #1 against artist #2 is not a duplicate to listing artist #2 against artist #1.

[ad_2]