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
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