Oracle Query Help Needed

[parlati]

Hello Folks,
I'm a not an expert in Oracle/SQL Server query and need some guidence and help from the professionals.

Basis for my challange is that I have two tables that i need to collect data from. The main table is ACTIVITIES...secondary table is CODESET...

Fields (column) To collect/ are...
TABLE: ACTIVITIES
*COLUMN: SEQ
*COLUMN: AN
*COLUMN: NET_ID
COLUMN: DES
COLUMN: R11

TABLE: CODESET
*COLUMN: CODE
*COLUMN: CONFIG_ID
*COLUMN: RFIELD_NR
COLUMN: SHORT

Fields marked '*' are primary keys. Field R11 are foreign key in ACTIVITIES Table and point to CODE in CODESET Table.

TABLEDATA (ACTIVITIES):
AN | NET_ID | DES | R11
TOP10 | 510 | TASKNAME 1 | 6
TOP11 | 511 | TASKNAME 2 | 7

TABLEDATA (CODESET):
CODE | CONFIG_ID | RFIELD_ID | SHORT
6 | 272 | 11 | YEAR 2008
6 | 273 | 11 | YEAR 2004
7 | 272 | 11 | YEAR 2009
7 | 273 | 11 | YEAR 2005

THE GOAL IS TO GET THE SHORT (DESCRIPTION) IN THE ACTIVITIES TABLE QUERY. Not sure what's the best way to do this, sub-query or join query.

Any help highly appreciated.
-Parlati

[cgyrob]

Just use a join query. Sub query to just pull fields is not required.

Select a.Seq, a.An, a.NET_Id, a.DES, b. Short
From Activities a, Codeset b
Where a.R11 = b.code

[parlati]

Thanks for the message. A join with a.R11=b.code is not sufficient -
because field CODESET.Code is not unique - primary fields field include code, config_id and rfield_nr. I was thinking about a join with select case - but not sure how yet. I still in process to work out something. If you already know - i very much appreciate your help.

Kind regards,
Parlati

[cgyrob]

I am not quite sure what you want your final result to look like?

The join i provided would give you the following result

TOP10 | 510 | TASKNAME 1 | 6 | YEAR 2008
TOP10 | 510 | TASKNAME 1 | 6 | YEAR 2004
TOP11 | 511 | TASKNAME 2 | 7 | YEAR 2009
TOP11 | 511 | TASKNAME 2 | 7 | YEAR 2005

If you can give me an example of what you want the final result to be I can help you find the solution.

Rob