SQL Subqueries
Subject:   SubQueries
Date:   2003-06-04 06:27:40
From:   anonymous2
Response to: SubQueries

What you have to decide is what row you want to get from the second table. If I am reading your description correctly, you have a two tables that have a one to many relationship. For the table that you want data that has the many relationship, you have to define (assuming you want one row from the "many" table) additional criteria. Once this is defined, then you can use 'MIN', MAX', or a specific value from the "many" table.
Ex -
FROM table1 a LEFT OUTER JOIN table2 b on
a.part = b.part
and b.column = select min(c.column)
from table2 c INNER JOIN
b.part = c.part

This query attempts to use a subquery to obtain the unique value from the 'many' table.