getting id from a given name in sql

Question

So, i have two tables

fahrlehrer(id_f,first name,last name)

and

fahrschueler(id,first name, last name,id_f).

I have the last name of a column in fahrlehrer. now my task is to show the first name and last name of all fahrschueler that have the FK of the the given name

my idea so far is:

select fahrschueler.nachname, fahrschueler.vorname 
from fahrschueler
join fahrlehrer
on fahrschueler.fahrlehrernr = (select fahrlehrernr from fahrlehrer where nachname = "Blechle");

My guess is that the brackets are set wrong as im not sure of the syntax, glad the learn the right syntax

if there is something unclear lmk

edit: i dont have datas, my task says: give out all first and last names of fahrschueler if their fahrleher's name is "Blechle"

the output shouldnt be a help since the output is only the names

Can you edit your question and add some simple data and the expected output — SelVazi, Mar 28 '23 at 22:49
Why do you keep undoing my edits that are supposed to make the question more readable? — RiggsFolly, Mar 28 '23 at 22:54
did i ? was not on purpose, sorry, i guess we edited the post the same time — Ranzini, Mar 28 '23 at 22:55

SelVazi · Answer 1 · 2023-03-28T23:28:28.460

0

You can do it using inner join to join your tables into one as follows :

select t1.nachname, t1.vorname 
from fahrschueler t1
join fahrlehrer t2 on t1.id_f = t2.id_f
where t2.nachname = 'Blechle'

edited Mar 28 '23 at 23:28

answered Mar 28 '23 at 22:55

SelVazi

10,028
2
13
29

score 0 · Accepted Answer · answered Mar 28 '23 at 22:57

select fahrschueler.nachname, fahrschueler.vorname 
from fahrschueler
    join fahrlehrer on fahrschueler.id_f = fahrlehrer.id_f
WHERE fahrlehrer.nachname = 'Blechle'

Join the 2 tables using the id_f and its alway best to use single quotes in a query around a string.

getting id from a given name in sql

2 Answers2