SQL how to select a colomn from a forgein key without join-CodePudding

I hope you guys can help me.

I have 2 tables 1 called Stuk and one is called Niveau.

in the table Stuk I have a FK from the tabel Niveau. This is the column niveaucode the skill codes I have are:

A EXAMPLE 1 B EXAMPLE 2 C EXAMPLE 3

If I select a musicpiece I dont want to see A, B or C I want to see EXAMPLE 1, EXAMPLE 2 or EXAMPLE 3. I have to do this without a JOIN

CodePudding user response：

SELECT S.[stuknr], S.[genrenaam],
CASE
  WHEN S.niveaucode='A' THEN 'EXAMPLE 1' 
  WHEN S.niveaucode='B' THEN 'EXAMPLE 2'
  WHEN S.niveaucode='C' THEN 'EXAMPLE 3'
  ELSE 'HMM'
END AS FLAG
FROM dbo.Stuk S 
WHERE  S.[jaartal] > 1995

CodePudding user response：

I guess this will answer your question:

select stuknr, componistid, titel, stuknrorigineel, genrenaam, omskrijving, speeldur, jaartal 
from stuk a, niveau b where a.niveaukode = b.niveaukode

Sorry if I misspelled any of your attributes (I didn't double check, and I don't know dutch)