none
Is there any way where we can map a an id to a field which is in a different table

    Question

  • Hi,

    My title may sound weird so does my question . But  i want to know is basically i have a table which has country code as IND and its desc as INDIA so in other table which has the codes i can join to this one and bring the description, But the primary table also has a code as IDA which also means INDIA but this desc is not in the secondary table and i cannot add anything to this database.

    So, is there anyway where i can query a SQL statement to say like if its IDA bring the desc as INDIA?

    Thanks,

    Sujith.

    Tuesday, July 02, 2013 5:57 PM

Answers

  • Is there a way?  Yes - but you must code for it specifically, it is not a recommended approach, and it should be considered a hack or a kludge. 

    select ...
    from primary_table as pri inner join countrycodes as ccd
    on (pri.ccode = ccd.ccode) or (pri.ccode = 'IDA' and and ccd.ccode = 'IND')
    ...

    Naomi has a much better suggestion - and I would suggest a review of how one SHOULD implement a relational database.

    • Marked as answer by SqlDev12 Tuesday, July 02, 2013 7:09 PM
    Tuesday, July 02, 2013 6:13 PM

All replies

  • I suggest to normalize the codes. Do an UPDATE in your primary table to 

    UPDATE PrimaryTable SET Code = 'IND' where Code = 'IDA'

    After that, make a foreign key relationship between the primary table and the table with codes and descriptions so you only will be able to use valid codes from now on.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Tuesday, July 02, 2013 6:04 PM
    Moderator
  • Is there a way?  Yes - but you must code for it specifically, it is not a recommended approach, and it should be considered a hack or a kludge. 

    select ...
    from primary_table as pri inner join countrycodes as ccd
    on (pri.ccode = ccd.ccode) or (pri.ccode = 'IDA' and and ccd.ccode = 'IND')
    ...

    Naomi has a much better suggestion - and I would suggest a review of how one SHOULD implement a relational database.

    • Marked as answer by SqlDev12 Tuesday, July 02, 2013 7:09 PM
    Tuesday, July 02, 2013 6:13 PM
  • Clean up the data and make it all ISO-3166 compliant. Why kludge dirty data forever? 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Tuesday, July 02, 2013 6:49 PM
  • Follow Naomi's advice. However, if you are working on a Reporting Database and cannot perform updates, consider adding a Table Variable where you could add any exception codes you may have e.g IDA for INDIA. Then when performing the SELECT do a COALESCE. If you have only one exception, follow Scott advice.

    SET NOCOUNT ON
    DECLARE @tblExceptions table (code varchar(3), descr varchar(20))

     INSERT @tblExceptions   values ('IDA', 'INDIA')
     INSERT @tblExceptions values('PHI', 'PHILIPPINES')
     
    SELECT  COALESCE(cc.descr, t.descr, 'NA') , p.someColumn
    FROM tblPrimary p
      LEFT OUTER JOIN tblCountryCode cc ON  p.code = cc.code
      LEFT OUTER JOIN @tblExceptions t ON  p.code =  t.code


    Tuesday, July 02, 2013 7:19 PM