Hi, I have these three tables currently in my database...
album
_____
album_id [INT (unsigned), PRIM KEY, AUTO-INC, INDEX]
artist_id [INT (unsigned), INDEX]
title_id [INT (unsigned), INDEX]
votes [INT (unsigned), INDEX]
artist
_____
artist_id [INT (unsigned), PRIM KEY, AUTO-INC, INDEX]
artistname [VARCHAR (100), INDEX]
title
_____
title_id [INT (unsigned), PRIM KEY, AUTO-INC, INDEX]
titlename [VARCHAR (150), INDEX]
This is the query I am currently using to extract the relevant information
from the tables...
SELECT album_id, artistname, titlename FROM album, artist, title
WHERE album.artist_id=artist.artist_id
AND album.title_id=title.title_id
ORDER BY votes DESC LIMIT 0, 25
However, I want to add a new table [detailed below] that differs from all
the other tables above in that an entry in 'album' table might not have a
corresponding entry in the new, album_comment table... looking at the new
table, what is the best way to structure a query that will return all the
information I need... i.e. all of the information returned by the original
query, as well as any 'comment_text' entries that might correspond with that
album [i.e. the album_id entry].
album_comment
_____
comment_id [INT(unsigned), PRIM KEY, AUTO-INC, INDEX]
album_id [INT (unsigned), INDEX]
comment_text [TEXT]
I have been experimenting with JOIN and NATURAL LEFT JOIN [which doesn't
seem to join more than two tables... otherwise the following would have been
perfect SELECT * FROM album NATURAL LEFT JOIN artist NATURAL LEFT JOIN title
NATURAL LEFT JOIN album_comment - is there any way I can make it this
simple?]
Many thanks for your time...
Adam Walton
album
_____
album_id [INT (unsigned), PRIM KEY, AUTO-INC, INDEX]
artist_id [INT (unsigned), INDEX]
title_id [INT (unsigned), INDEX]
votes [INT (unsigned), INDEX]
artist
_____
artist_id [INT (unsigned), PRIM KEY, AUTO-INC, INDEX]
artistname [VARCHAR (100), INDEX]
title
_____
title_id [INT (unsigned), PRIM KEY, AUTO-INC, INDEX]
titlename [VARCHAR (150), INDEX]
This is the query I am currently using to extract the relevant information
from the tables...
SELECT album_id, artistname, titlename FROM album, artist, title
WHERE album.artist_id=artist.artist_id
AND album.title_id=title.title_id
ORDER BY votes DESC LIMIT 0, 25
However, I want to add a new table [detailed below] that differs from all
the other tables above in that an entry in 'album' table might not have a
corresponding entry in the new, album_comment table... looking at the new
table, what is the best way to structure a query that will return all the
information I need... i.e. all of the information returned by the original
query, as well as any 'comment_text' entries that might correspond with that
album [i.e. the album_id entry].
album_comment
_____
comment_id [INT(unsigned), PRIM KEY, AUTO-INC, INDEX]
album_id [INT (unsigned), INDEX]
comment_text [TEXT]
I have been experimenting with JOIN and NATURAL LEFT JOIN [which doesn't
seem to join more than two tables... otherwise the following would have been
perfect SELECT * FROM album NATURAL LEFT JOIN artist NATURAL LEFT JOIN title
NATURAL LEFT JOIN album_comment - is there any way I can make it this
simple?]
Many thanks for your time...
Adam Walton