CODEWITHSUNDEEP
mysqlsql
nniks19
nniks19

Reputation: 45

SQL - Passing column in subquery

my command:

Select sobe.Id, sobe.Naziv, sobe.Opis, sobe.Kat, (Select Group_CONCAT(studenti.Ime,studenti.Prezime) ImePrezime FROM studentsoba LEFT JOIN studenti ON studentsoba.Id_Sobe=sobe.Id AND studenti.JMBAG = studentsoba.JMBAG) AS ImePrezime from Sobe

This gives me this error:

1054 - Unknown column 'sobe.Id' in 'on clause'

I see what the problem is but I cant figure out how to fix it. I want to pass sobe.Id inside of this subquery:

(Select Group_CONCAT(studenti.Ime,studenti.Prezime) ImePrezime FROM studentsoba LEFT JOIN studenti ON studentsoba.Id_Sobe=sobe.Id AND studenti.JMBAG = studentsoba.JMBAG)

I want to check which students are in that room. Word soba means room and JMBAG is like personal number for each student

Upvotes: 0

Views: 42

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269583

Try moving it to a WHERE clause:

Select s.Id, sobe.Naziv, s.Opis, s.Kat,
       (Select Group_CONCAT(si.Ime, si.Prezime) as ImePrezime
        from studentsoba ss LEFT JOIN
             studenti si
             on si.JMBAG = ss.JMBAG
        where ss.Id_Sobe = s.Id 
       ) AS ImePrezime
from Sobe s;

The correlated subquery should be fine. I suppose the correlation clause cannot be in the on clause -- I normally put it in the where clause anyway.

Also note that I introduced table aliases so the query is easier to write and to read.

Upvotes: 1

Related Questions