MS SQL, couple table, linked to other table.

[wigga]

i must get the tracks in all playlists, than i must get the artist and album name... (AfLijsten means Playlist)


i have the following install:

use master;
go
if db_id('dbGuus') is not null drop database dbGuus;
go
create database dbGuus;
go
use dbGuus;
go
create table tblAlbums( alId int identity(1,1) primary key,
alNm varchar(16) not null,
constraint uAlbum unique (alNm));
go
create table tblArtists( arId int identity(1,1) primary key,
arNm varchar(16) not null);
go
create table tblTracks( trId int identity(1,1) primary key,
alId int not null references tblAlbums(alId),
trTitle varchar(16) not null,
trTime int not null,
arId int not null references tblArtists(arId),
trGenre varchar(16) not null,
trFile varchar(max) not null,
constraint uTrack unique (alId, trTitle));
go
create table tblAfLijsten( afId int identity(1,1) primary key,
afNm varchar(16) not null,
constraint uAfLijst unique (afNm));
go
create table tblAfTracks( afId int not null references tblAfLijsten(afId),
trId int not null references tblTracks(trId),
constraint pkAfTrack primary key (afId, trId));
go

than i try this query:

select tblTracks.trId, trTitle, tbllAlbums.alNm, tblArtists.arNm, trGenre, trTime, tblAfTracks.afId from tblAfTracks, tblTracks, tblArtists where tblTracks.trId=tblAfTracks.trId and tblArtists.arId=tblTracks.arId and tblAlbums.alId=tblTracks.alId

and get this error:

Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "tblAlbums.alId" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "tbllAlbums.alNm" could not be bound.

[dannystommen]

In your both you 'select' as 'were' clausule you use something from tblAlbum ('and tblAlbums.alId=tblTracks.alId'), while tblAlbums is not in your 'from' clausule. That's the reason why it cannot be found.

Secondly, I would reccomend using table aliases to make your select more readable and short. Also typ the SQL prereserved word with capital characters.


SELECT t.trId, t.trTitle, alb.alNm, art.arNm, t.trGenre, trTime, aft.afId
FROM tblAfTracks aft, tblTracks t, tblArtists art, tblAlbum alb
WHERE t.trId=aft.trId
AND art.arId=t.arId
AND alb.alId=t.alId

[ComITSolutions]

Since Table tblAlbums is not Used in query you are getting this error as mentioned by dannystommen.

Practice writing query using Joins!! by which relation can be easily traced.

select tblTracks.trId, trTitle, tbllAlbums.alNm, tblArtists.arNm, trGenre, trTime, tblAfTracks.afId
from tblAfTracks Inner Join tblTracks On tblTracks.trId=tblAfTracks.trId
Inner Join tblArtists On tblArtists.arId=tblTracks.arId
Inner Join tblAlbums On tblAlbums.alId=tblTracks.alId