質問する

BenBase's profile - activity

2019-06-03 04:29:19 +0200 バッジを受け取った  卓越した質問 (source)
2019-06-02 07:15:32 +0200 バッジを受け取った  サポーター (source)
2019-06-02 07:13:39 +0200 コメント付き回答 Multiple Outer Joins on Multiple Tables

Perfect. Thank you!

2019-06-02 07:13:18 +0200 ベストアンサーマーク Multiple Outer Joins on Multiple Tables

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"

2019-06-02 02:51:40 +0200 バッジを受け取った  人気の質問 (source)
2019-06-01 20:40:37 +0200 バッジを受け取った  生徒 (source)
2019-06-01 06:37:54 +0200 コメント付き質問 Multiple Outer Joins on Multiple Tables

C:\fakepath\Music_test.odb

2019-06-01 06:37:43 +0200 編集された質問 Multiple Outer Joins on Multiple Tables

Multiple Outer Joins on Multiple Tables Trying to write a Query that has multiple outer joins on multiple tables. I am u

2019-06-01 06:32:46 +0200 編集された質問 Multiple Outer Joins on Multiple Tables

Multiple Outer Joins on Multiple Tables Trying to write a Query that has multiple outer joins on multiple tables. I am u

2019-06-01 06:32:46 +0200 バッジを受け取った  編集者 (source)
2019-06-01 06:27:56 +0200 質問をする Multiple Outer Joins on Multiple Tables

Multiple Outer Joins on Multiple Tables Trying to write a Query that has multiple outer joins on multiple tables. I am u

2019-06-01 04:54:21 +0200 バッジを受け取った  卓越した質問 (source)
2018-07-05 18:50:43 +0200 バッジを受け取った  人気の質問 (source)
2018-07-02 02:15:41 +0200 コメント付き回答 Embedded "Table-in-table" View

Thank you, will go about doing it that way.

2018-07-02 02:15:20 +0200 ベストアンサーマーク Embedded "Table-in-table" View

In Microsoft Access you are able to see a "table within table" view. Was hoping this was possible in Base. Included a picture of what I mean.

You can see I have two tables. One is Artist and the other is Album. When I open the Artist table there is a plus sign that can expand and show the Albums from the Album table that match that Artist.

image description

I realize I could generate a report to get this type of view. However I like how in Access I can get this view in the normal table view so that I can add new entries right there and not have to swap between windows to see the report and then go somewhere else to add additional data.

2018-07-02 02:15:20 +0200 バッジを受け取った  学者 (source)
2018-07-02 02:15:05 +0200 コメント付き質問 Embedded "Table-in-table" View

Sorry, clicked the checkbox to see what the difference was and then forgot to uncheck.

2018-07-01 21:47:07 +0200 編集された質問 Embedded "Table-in-table" View

Embedded "Table-in-table" View In Microsoft Access you are able to see a "table within table" view. Was hoping this was

2018-07-01 21:46:45 +0200 質問をする Embedded "Table-in-table" View

Embedded "Table-in-table" View In Microsoft Access you are able to see a "table within table" view. Was hoping this was