Ask Your Question
1

Multiple Outer Joins on Multiple Tables

asked 2019-06-01 06:27:56 +0200

BenBase gravatar image

updated 2019-06-01 06:37:43 +0200

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"

edit retag flag offensive close merge delete

Comments

BenBase gravatar imageBenBase ( 2019-06-01 06:37:54 +0200 )edit

1 Answer

Sort by » oldest newest most voted
2

answered 2019-06-01 18:29:19 +0200

Ratslinger gravatar image

Hello,

This should work for you:

SELECT "Artist"."ArtistID",
       "Artist"."ArtistName",
       "Singles Count",
       "Album Count",
       "Album Average"
FROM "Artist"
  LEFT JOIN (SELECT "ArtistID",
                    COUNT("ArtistID") "Singles Count"
             FROM "Singles"
             GROUP BY "ArtistID") A ON "Artist"."ArtistID" = "A"."ArtistID"
  LEFT JOIN (SELECT "ArtistID",
                    COUNT("ArtistID") "Album Count",
                    AVG("Rating") "Album Average"
             FROM "Album"
             GROUP BY "ArtistID") B ON "Artist"."ArtistID" = "B"."ArtistID"

I rather work with SQL directly and not use the GUI.

edit flag offensive delete link more

Comments

Perfect. Thank you!

BenBase gravatar imageBenBase ( 2019-06-02 07:13:39 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2019-06-01 06:27:56 +0200

Seen: 27 times

Last updated: Jun 01