Combine multiple rows into one

[robojam]

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

[IUnknown]

What DB are you using?