[PHPwestoz] [OT] Relational SQL Databases

Nicolas Connault nicou at sweetpeadesigns.com.au
Tue Feb 22 16:06:02 UTC 2005


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




More information about the PHPwestoz mailing list