• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 232
  • Last Modified:

Tricky SELECT statement

I need to create  a SELECT statement against two tables:

Table 1: Languages
Language_ID
Language_Descr

Table 2: Narrators_Languages
Narrator_ID
Language_ID

I need to return all records in the Languages table, and specify a Narrator_ID from the Narrators_Languages table. For each record, return 'YES' if there is a record for the specified Narrator_ID else return 'NO'.

Example:
Languages 'EN', 'FR', 'SP'
Narrators_Languages: Narrator_ID = 2 and Language_ID = 'EN', Narrator_ID = 2 and Language_ID = 'SP'
(no record for Narrator_ID = 2 and Language_ID = 'FR')

Desired result:
Narrator_ID  Language_ID  Match
    2                'EN'                YES
    2                'SP'                YES
    2                'FR'                 NO

Thanks for any help with this!
0
wlevy
Asked:
wlevy
2 Solutions
 
imran_fastCommented:

select A.Narrator_ID,A.Language_ID,case when N.Language_ID is null then 'No' else 'Yes' end Match
FROM
(select Distinct N.Narrator_ID,  L.Language_ID
from Narrators_Languages N
cross join
Languages L)  A
left outer join Narrators_Languages N
on A.Narrator_ID = N.Narrator_ID
and N.Language_ID = A.Language_ID

0
 
imitchieCommented:
declare @narrator_id int set @narrator_id = 2   --- <-- specify a Narrator_ID

select @narrator_id, l.language_id,
 case when n.narrator_id is null then 'NO' else 'YES' end as Match
from languages l
 left join narrators_languages n
  on l.language_id = n.language_id and n.narrator_id = @narrator_id
order by 3 desc, 2 asc
0
 
wlevyAuthor Commented:
Both excellent - thank you very much! I was close to getting this on my own but not quite there... this is perfect.
0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now