Re: [PHPwestoz] [OT] Relational SQL Databases

[Nicolas Connault <nicou@sweetpeadesigns.com.au>]

Timothy White wrote:

> Music.Artists
> ID   |   Artist
> 1    |   Avril
> 2    |   Baxtar
> 3    |   Zebra
> ...
> Music.Albums
> ID   |   Album   | Artist
> 1    |   Pink    | Baxtar
> 2    |   Mouse   | Baxtar
> 3    |   Rock    | Zebra
> Music.Songs

Tim,

The design of your database doesn't take advantage of your ID fields. I 
suggest you change it to this:

Music.Artists
ID   |   Artist
1    |   Avril
2    |   Baxtar
3    |   Zebra
...
Music.Albums
ID   |   Album   | ArtistID
1    |   Pink    | 2
2    |   Mouse   | 2
3    |   Rock    | 3
...
Music.Songs
ID  |  Song  |  AlbumID
1  |  song1  |  1
2  |  song2  |  1
3  |  song3  |  3

As for the SELECT query, try something in this pattern:

SELECT Songs.*, Albums.* FROM Songs, Albums, Artists
WHERE Artists.ID = Songs.ArtistID
AND Album.ID = Songs.AlbumID
AND Artist.Artist = "Avril"

This needs to be tuned up to your needs, I think it will return 
duplicate records, so you need to play around with the conditions. I've 
only put these to illustrate how to link tables to each other.

Cheers

Nicolas