Article:		SQL Subqueries
Subject:		SubQueries
Date:		2003-02-27 17:51:37
From:		john1948
I have 2 sql tables. (1) One Table contains single part numbers. In this table the part numbers are unique. (2) The Second table contains multiples of the single part number in the first table. This table contains values in the second part record that I need to access. I have written a procedure to get at this data in a cursor. This cursor however take over 6 minutes to update the 1st table. The second issue is that the first table only contains about 3,000 records. I see no easy way of accessing this second record. Do you?

Showing messages 1 through 1 of 1.

• SubQueries
  2003-06-04 06:27:40  anonymous2 [Reply | View]
  
  
  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 -
  SELECT *
  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.