CODEWITHSUNDEEP
sqljsont-sqlmany-to-many
צח חורי
צח חורי

Reputation: 401

T-SQL Query Many to Many

I have 3 tables:

doctors:
-----------
id
name

language:
-----------
id
name

languageDoctors:
----------------
doctor_id
language_id

I would like to return a JSON with the doctor name and after it an array of the languages that the doctor has.

Upvotes: 0

Views: 37

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269633

Well, you can use for json path to construct the json:

select d.*,
       (select l.name as language
        from doctor_languages dl join
             languages l
             on dl.language_id = l.language_id
        where dl.doctor_id = d.doctor_id
        for json path
       ) as languages
from doctors d;

Here is a db<>fiddle.

Upvotes: 1

Related Questions