Is it possible to JOIN 3 tables ?

[MichaelHe]

Is it possible to JOIN 3 tables.

I have a discography with these tables

CD
- CDid
- CDtitle

SONG
- SongID
- SongTitle

TRACKLIST
- idCD
- idSong

This is put together so when a CD is shown it gets the songs on it from the TRACKLIST table.

Then I have a Person page where all the person who worked on the songs are listed.
It uses these tables

PERSON
- PersonID
- PersonName

WORK
- WorkID
- PersID
- Work (ex. Guitar)
- Song (the id of the song the work is done)

This is put together so when you click a person it writes the work this person has done on which songs.
And if you click a song title it writes which person did what on that song.

BUT now I got the idea to writes all the person who worked on a entire album.
Here is my problem.
I can easier list the person and there work on ex. CD 5 by JOINING WORK and TRACKLIST where TRACKLIST.idCD = 5

But then I can't alphabetice by PersonName.
If I need it alphabeticed by PersonName i need the PERSON table JOINED with the WORK and TRACKLIST tables.
And also if I need it alphabeticed by WORK.Work, PERSON.PersonName

So is it possible to JOIN 3 tables ?

I could add a CD field in the WORK table, but the problem is that the songs are on more than one CD. So I have to get the tracklist on exactly CD 5 to know which songs are on it, and then get the Work on these songs.
But if I do that in 2 steps I can't alphabetice like I want it.
Therefore I need all 3 tables JOINED.

Hope my explanation was understandable 🙂

best
Michael

[frost110]

So the table should look like this

CD
 -cdid
 -cdtitle

song
 -songid
 -songtitle

track
 -trackid
 -cdid
 -songid

This is the SQL statement
SELECT * FROM cd c, song s, track t WHERE c.cdid = 5 AND c.cdid = t.cdid AND s.songid = t.songid;

--FrosT

[MichaelHe]

But what about the WORK and PERSON tables ?

[edwardsbc]

The short answer is "Yes" you can JOIN almost any number of tables. You didn't say what database system you are using, but JOIN syntax has two form, the simple WHERE tbl1.fld1=tbl2.fld2 form and the explicit JOIN form. I usually use the JOIN form for 3+ tables to help keep my head straight. You have to be very careful about what you join where, and you usually need to be explicit about it. Do you know if you are doing a FULL OUTER, LEFT, RIGHT, or INNER join? Otherwise you may not get the result you were expecting.

I would suggest some hard study of your database systems SQL documentation.

[bpat1434]

Heh... Frost, you are correct; however, you are using an old and non-standard way of joining tables. The SQL92 format specifies that you define the JOIN clause and an ON or USING clause. This is what is globally used in all DBMS.

So a global query would look something like:

SELECT a.CDtitle cd, b.Songtitle song, c.PersonName name, d.Work work
FROM `tracklist` z
INNER JOIN `cd` AS a
  ON z.idCD = a.CDid
INNER JOIN `song` AS b
  ON z.idSong = b.SongID
INNER JOIN `Work` AS d
  ON z.idSong = d.Song
INNER JOIN `Person` AS c
  ON d.PersID = c.PersonID
WHERE d.WorkID = '$id'
  OR c.PersonName LIKE '%$name%'
  OR b.SongTitle LIKE '%$title%'
  OR a.CDTitle LIKE '%$cd%'
ORDER BY title ASC

Yes it's extremely long, but it joins all 5 tables onto eachother, and all the information should be available.... The base table would be track and everything would join on that and work tables respectively.

The result-set (when just dumped) would look like the following (tabularly):

+--------------------------------------------+
| cd   |   song    |    name    |    work    |
+--------------------------------------------+
|                                            |

[Lars_Berg]

however, you are using an old and non-standard way of joining tables. The SQL92 format specifies that you define the JOIN clause and an ON or USING clause.

That is not correct.

select c
from t,q
where q.c1 = t.c1

is standard compliant.

[bpat1434]

well, yes it is supported in the ANSI standard; however, it's an old deprecated format that is interpreted differently by each individual DBMS. To make sure that the query is interepreted the same on all DMBS's, you'd want to specify the JOIN and ON/USING clause so as to not "confuse" the DBMS as to what you want.

Don't believe me? Pick up an SQL book [one that doesn't focus on any particular DBMS] like "Learning SQL" by Beaulieu and flip to page 79/80. You'll read:

This older method [speaking of the ones above] does not include the on subclause; instead, tables are named in the from clause separated by commas, and join conditions are included in the where clause. While you may decide to ignore the SQL92 syntax in favor of the older join syntax, the ANSI join syntax has the following advantages:

• Join conditions and filter conditions are separated into two different clauses (the on subclause and the where subclause, respectively), making a query easier to understand

• The join conditions for each pair of tables is contained in its own on clause, making it less likely that part of a join will be mistakenly excluded

• Queries that use the SQL92 join syntax are portable across database servers, whereas the older syntax is slightly different across the different servers.

The benefits of the SQL92 join syntax are easier to identify for complex qureies [like this one] that include both join and filter conditions.

So he's not wrong, and I never said he was. I just simply said it would be better to use the ANSI standard that way he knew exactly what the server should return, and can "debug" easier.

It is a non-standard syntax, it's not ANSI SQL92 which expects the format I've given. SQL1 or any older version of the standard would have this as its standard (speculating a bit). But since most DBMS (mySQL, PostgreSQL, MS SQL Server 2003, Oracle, IBM DB2, Sybase) all have been updated since 1992, they've adopted most of the ANSI SQL92 syntax (mySQL would be the one that is further behind with the EXCEPT clauses). But that's a whole 'nother story....

[Lars_Berg]

Specifying an inner join in the where clause is not deprecated in the standard specification and not interpreted differently by different vendors. (If Beaulieu claims that he is incorrect.)
Maybe you are thinking of the different ways to specify outer joins in the where clause which is non standard.

[bpat1434]

It's still better to be specific, rather than ambiguous... I know I'd prefer to see that I'm joining table X onto table Y ON (or using) column = column. Makes for cleaner code, and something that is more understandable... especially to a new PHP/SQLer....

I'm pretty sure it was just general joins (mainly because OUTER and RIGHT joins are talked about later) and that the old syntax is "out of style" because there is now an updated standard that you should follow. I try to follow the new standards, as most of the old standards become deprecated and dropped in updates...

It's a preference thing, and I apologize for the ambiguity of the reply about deprecated functions and what not...