Left Join trouble...

[Seabass]

Hi there,

I'm having some difficulty with a fairly complicated Left Join im trying to put together, and keep getting the following error:

Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "crps.standard_phr_id" could not be bound.

As a bit of background, I've got three tables I'm dealing with here:

COUNTRY_LANGUAGE -> Contains a list of languages and the countries they are used in, each country/language combo is represented by a code.
PHRASES -> A list of phrases in a table, with a phrase id (standard_phr_id)
PHRASES_TRN -> Contains translations of the phrases in the table above, linked using the standard_phr_id, and each row contains the language code of the translation too.

There is a fair bit more going on around this but I don't think it's relevant here. So, what I'm trying to do is produce a list of all phrases and their translations for each language:

standard_phr_id, phrase_text, language_code, translation_text

in an effort to identify where translations are missing (due to a NULL in the translation_text column). Here's what my query looks like so far:

SELECT distinct crpt.hazard_id as item_id,
'Risk' as item_type,
crps.category as sub_type,
crpt.language_code as language_code,
crps.std_phr_name as base_key,
convert(nvarchar(4000), crps.std_phr) as base_text,
convert(nvarchar(4000), crpt.std_phr) as trn_text,
'N' as actioned
FROM PHRASES crps,
COUNTRY_LANGUAGE cl

LEFT JOIN (SELECT t.hazard_id as hazard_id,
t.standard_phr_id as standard_phr_id,
t.language_code as language_code,
t.std_phr as std_phr
FROM PHRASES_TRN t,
COUNTRY_LANGUAGE l
WHERE t.language_code = l.language_code) as crpt
ON crps.standard_phr_id = crpt.standard_phr_id

WHERE (crps.category like '%Risk%' or
crps.category like '%Sensitizer%')
and crps.category not like '%CN%'

I've read up on the error I'm getting and it seems to talk about typos (which I'm pretty sure isn't the problem here), or some other form of multi part error. Any help with this would be hugely apprciated.

Cheers,
Seb

[ComITSolutions]

in your query first part Distinct you have not selected field standard_phr_id .

[tbd123]

Hi Seabass

have you solved this issues?