Given the contents of table1 and table2, you can't get the value
"NAME_I_WANT" because its SUB_CODE (1000) does not appear in table1. You
will only get "SUB_NAME", corresponding to SUB_CODE 1111, which appears in
both tables.

At 04:04 PM 2/29/2004, you wrote:

># View Group Archive: http://ITtoolbox.com/hrd.asp?i=521>
>Hi All,
>
>I have two tables that I am joining. (table1 and table2) In the table
>two I want to get the control_name of the control_code of the sub_code:
>
>TABLE1
>
>SUB_CODE
>1111
>
>TABLE2
>
>SUB_CODE CONTROL_CODE CONTROL_NAME
>1000 ALL NAME_I_WANT
>1111 1000 SUB_NAME
>
>So, how would I write my query to get 'NAME_I_WANT'??
>
>Thanks for any help you might offer.
>

Unless my mind fails me, if you write the query either with 1) or 2),
you'll get two rows:

NAME_I_WANT
SUB_NAME

I "think" the request was to be able to get only one row - which Deepa's
query will do correctly. Of course being able to use this solution in
the real world all depends on Table2 only having two levels of hierarchy
in it - that part we don't know for sure from the requirements.

Deepa,
You are refering to c.SUB_CODE but you havent defined alias 'c'. also as
table SUB_CODE value in table2 which is 1000 for control_name
"NAME_I_WANT" is not there in Tabel1. So, you will nto get the desired
result.

Copyright 1998-2015 Ziff Davis, LLC (Toolbox.com). All rights reserved. All product names are trademarks of their respective companies. Toolbox.com is not
affiliated with or endorsed by any company listed at this site.