请求协助实现三张数据库表的关联查询（附Java参考代码）

Hey Juliee, let's build the exact SQL query you need to feed your Java code, based on what you already know about 2-table joins and your database structure.

First, let's recap what your Java code expects from the result set:

• IdOrg (from the collabo table)
• CodeA, TitreA, DateArrivee (from the album table)
• IdentC (both int and string values—this looks like a possible typo in your Java code, probably meant to pull an ID and a name field from chanteur, like IdentC and NomC? We'll adjust for that in the example below)

Your Existing 2-Table Join #

You already have this working:

SELECT IdOrg, A.CodeA 
FROM collabo A, album C 
WHERE A.CodeA = C.CodeA 
ORDER BY 1

Expanding to 3 Tables (Including chanteur) #

We just need to add the chanteur table and link it to album using the IdentC foreign key. Here are two ways to write this:

Option 1: Using your familiar comma-separated table syntax

SELECT 
    collabo.IdOrg,
    album.CodeA,
    album.TitreA,
    chanteur.IdentC,  -- Matches your Java getInt("IdentC")
    chanteur.NomC,    -- Replaced with a string field for getString() fix
    album.DateArrivee
FROM collabo, album, chanteur
WHERE 
    collabo.CodeA = album.CodeA  -- Link collabo to album
    AND album.IdentC = chanteur.IdentC  -- Link album to chanteur
ORDER BY collabo.IdOrg

Option 2: Using modern JOIN syntax (recommended for readability)

This format makes table relationships explicit and easier to maintain:

SELECT 
    c.IdOrg,
    a.CodeA,
    a.TitreA,
    ch.IdentC,
    ch.NomC,
    a.DateArrivee
FROM collabo c
INNER JOIN album a ON c.CodeA = a.CodeA
INNER JOIN chanteur ch ON a.IdentC = ch.IdentC
ORDER BY c.IdOrg

Key Notes for Your Java Code #

• Double-check that column names in the SELECT clause match exactly what you're calling in resu.getInt()/resu.getString()/resu.getDate(). If your original getString("IdentC") wasn't a typo, swap ch.NomC back to ch.IdentC in the query.
• Table aliases (like c for collabo, a for album) keep the query concise and easier to read.

Database Structure Reference #

内容的提问来源于stack exchange，提问作者juliee