dcsimg
CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 3 of 3

Thread: Left Join trouble...

  1. #1
    Join Date
    Aug 2011
    Posts
    1

    Left Join trouble...

    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

  2. #2
    Join Date
    Feb 2008
    Location
    Bangalore
    Posts
    149

    Re: Left Join trouble...

    in your query first part Distinct you have not selected field standard_phr_id .
    Encourage the efforts of fellow members by rating

    Lets not Spoon Feed and create pool of lazy programmers

    - ComIT Solutions

  3. #3
    Join Date
    Sep 2011
    Posts
    1

    Re: Left Join trouble...

    Hi Seabass

    have you solved this issues?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Windows Mobile Development Center


Click Here to Expand Forum to Full Width




On-Demand Webinars (sponsored)