Oracle

Oracle Query Help Needed

Please support our Oracle advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

•

Join Date: Jul 2009

Posts: 2

Reputation: parlati is an unknown quantity at this point

Solved Threads: 0

parlati

Offline

Newbie Poster

Oracle Query Help Needed

Jul 24th, 2009

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

Last edited by parlati; Jul 24th, 2009 at 4:51 am. Reason: Table data not properly seperated with spaces

•

Join Date: Sep 2008

Posts: 123

Reputation: cgyrob is on a distinguished road

Solved Threads: 18

cgyrob

Offline

Junior Poster

Re: Oracle Query Help Needed

Jul 24th, 2009

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

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
SELECT a.Seq, a.An, a.NET_Id, a.DES, b. Short
FROM Activities a, Codeset b
WHERE a.R11 = b.code
Select a.Seq, a.An, a.NET_Id, a.DES, b. Short
From Activities a, Codeset b
Where a.R11 = b.code

Last edited by peter_budo; Jul 24th, 2009 at 2:53 pm. Reason: Correcting closing tag

•

Join Date: Jul 2009

Posts: 2

Reputation: parlati is an unknown quantity at this point

Solved Threads: 0

parlati

Offline

Newbie Poster

Re: Oracle Query Help Needed

Jul 26th, 2009

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

•

Join Date: Sep 2008

Posts: 123

Reputation: cgyrob is on a distinguished road

Solved Threads: 18

cgyrob

Offline

Junior Poster

Re: Oracle Query Help Needed

Jul 26th, 2009

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