Trying to write a Query that has multiple outer joins on multiple tables. I am using HSQLDB and am struggling with the syntax.
I have three tables. Artist, Album, and Singles
Artist
+----------+------------------------+
| ArtistID | ArtistName |
+----------+------------------------+
| 103 | BandWith3Singles |
| 104 | BandWith1Album |
| 105 | BandWith2Albums1Single |
| 106 | BandWithNoMusic |
+----------+------------------------+
Album
+----------+---------+-----------------------+--------+
| ArtistID | AlbumID | AlbumName | Rating |
+----------+---------+-----------------------+--------+
| 104 | 116 | First Album by 104 | 3 |
| 105 | 117 | First Album by 105 | 2 |
| 105 | 118 | Second Album from 105 | 5 |
+----------+---------+-----------------------+--------+
Singles
+----------+--------+--------------------+-----------------------------+
| ArtistID | songID | SongName | AlbumName |
+----------+--------+--------------------+-----------------------------+
| 103 | 3 | Single 103 | Single 103 |
| 103 | 4 | Another 103 Single | Single EP |
| 103 | 5 | Final 103 Single | Single Jams |
| 105 | 6 | Awesome Song | Xtreme Awesomeness Volume 1 |
+----------+--------+--------------------+-----------------------------+
My Query
SELECT
"Artist"."ArtistName",
COUNT( "Album"."AlbumID" ) "Total Albums",
COUNT( "Singles"."songID" ) "Total Singles",
AVG( "Album"."Rating" ) "Average Album Rating"
FROM
{ oj
"Album" RIGHT OUTER JOIN "Artist" ON "Album"."ArtistID" = "Artist"."ArtistID"
LEFT OUTER JOIN "Singles" ON "Singles"."ArtistID" = "Artist"."ArtistID"
}
GROUP BY "Artist"."ArtistName"
My Query Results
+------------------------+--------------+---------------+----------------------+
| ArtistName | Total Albums | Total Singles | Average Album Rating |
+------------------------+--------------+---------------+----------------------+
| BandWith3Singles | 0 | 3 | |
| BandWith1Album | 1 | 0 | 3 |
| BandWith2Albums1Single | 2 | 2 | 3 |
| BandWithNoMusic | 0 | 0 | |
+------------------------+--------------+---------------+----------------------+
You can see the Query fails when an Artist has an album and a single, in which case the number of singles is always the same as the number of albums.
I’ve also attached a sample DB to help. The query not working is named “Artist_Summary_with_Singles_NOTWORKING”