I have two tables that I need to join to get the required data, but I get too many rows returned with everything I have tried. Basically, both tables have and IDEA_ID field, and a simple SELECT query on TABLE1 will give me the data that I want from that table, but I get too many rows returned from TABLE2 as there are multiple rows with the same IDEA_ID in TABLE2.

Basically,

SELECT *
FROM TABLE1

gives me all the data I need with the exception of 6 columns for which the data is in TABLE2.

TABLE2 has 3 columns - IDEA_ID, ROLE_ID and ADATE. IDEA_ID matches the IDEA_ID field in TABLE1; ROLE_ID is a number from 1 to 6; and ADATE is a date. There can be 1 to 6 rows for each IDEA_ID, with the IDEA_ID and ROLE_ID combination being unique.

How can I run a query that will give me rows that have the fields from TABLE1, and 6 additional columns from TABLE2?

Table structures are:

TABLE1
IDEA_ID, PROD_TYPE, TITLE, DESC

TABLE2
IDEA_ID, ROLE_ID, ADATE

Which six columns do you need from the second table?

-PatP

Pat

For each IDEA_ID in TABLE2, there are potentially 6 rows with that IDEA_ID, but with a number from 1 to 6 in the ROLE_ID column.

What I'm trying to do is to create a dataset that has all of the columns from TABLE1, but I want to create 6 new columns with data from TABLE2. Each of the new columns will represent a row from TABLE2 for each of the values of ROLE_ID from 1 to 6. The value that will go into each of these columns is the value in ADATE.

I can query TABLE2 to get these values:

SELECT idea_id,
decode(role_id, 1, adate),
decode(role_id, 2, adate),
decode(role_id, 3, adate),
decode(role_id, 4, adate),
decode(role_id, 5, adate),
decode(role_id, 6, adate)
FROM TABLE2

but this produces 6 rows of output. What I want from TABLE2 is a single row with each of these values. The query above produces a dataset with IDEA_ID plus 6 columns, each row having only one of those 6 column containing a value.

How do I produce just one row of output with all 6 of these columns having a value in it?