SQL to extract data from multiple tables to display in ASP Webpage

[madman070578]

I'm trying to extract the data from a simple relational database and display it in a table. I can code the sql to display all data from a single table, but not multiple tables.

The database is to store a music collection. The field descriptions are in the square brackets.

It has 4 tables -
Main Table - TblMusic
Fields - AlbumID (Primary Key)
Name [Album Name]
ArtistId(Link field to TblArtist)
NatId [Nationality of Group/Artist] (Link field to TblNationality)
RDate [Album Release Date]
Tracks [Number of Tracks on Album]
Image [Filename for image of CD Cover]
GenreId [Type of music] (Link field to TblGenre)

Genre Table - TblGenre
GenreId (Primary Key) Link Field
Genre [Type of Music]

Artist Table - TblArtist
ArtistId (Primary Key) Link Field
Artist [Artist/Group Names]
Description [Description of Artist]

Nationality Table - TblNationality
NatId (Primary Key)
Nationality [Nationality of Artist]

Can someone help me in working out the SQL to get the following information displayed in a ASP table......

Album Name, Artist Name, Artist Description, Artist Nationality, Album Release Date, Number of Tracks, and Genre.

Many thanks to all.....

[chrismartz]

If you use some JOINS to the tables you should be able to do this.

[russell_g_1]

something like this will do it. i've used the old style join syntax here, you might be able to do it differently from this depending on what type of database you've got. but anyway, it should work whatever you've got.

select m.album,a.artist,a.description,n.nationality,m.rdate,m.tracks,g.genre
from tblMusic m,tblgenre g,tblartist a,tblnationaily n
where m.genreid = g.genreid and m.artistid = a.artistid and n.natid = m.natid

btw, i notice you've got your nationality stored on the album rather than the artist. this doesn't look right to me...

[madman070578]

Russell,

I am using an Access Database - you are corect about the nationality, that should be with the artist :) Too many late nights!

Hopefully your SQL will work with an Access Database :)

[chrismartz]

That statement should have no problem with Access.