Join Question

I am trying to join two tables and one table could have multiple records.
Is there a way to limit to one record on the JOIN statement?
ThanksSELECT TOP 1 t1.col1, t1.col2, t1.colN
FROM
table1 AS t1 JOIN table2 AS t2 ON t1.col1 = t2.col1
"jack" wrote:

> I am trying to join two tables and one table could have multiple records.
> Is there a way to limit to one record on the JOIN statement?
> Thanks
>
>|||On Fri, 14 Oct 2005 13:11:47 -0600, jack wrote:

>I am trying to join two tables and one table could have multiple records.
>Is there a way to limit to one record on the JOIN statement?
>Thanks
>
Hi Jack,
Unfortunately, SQL Server has no "GimmeOneDontCareWhich" function. If
you want one from the group, you'll have to specify which one.
Here's a possible way to do what you want. I'll assume that you join on
col1 and want to join on only the row with the lowest col2.
SELECT ....
FROM table1 AS t1
INNER JOIN table2 AS t2
ON t1.col1 = t2.col1
WHERE t2.col2 = (SELECT MIN(col2)
FROM table2 AS t2b
WHERE t2b.col1 = t2.col1)
BTW, it's easier to reply if you provide some tables and sample data to
work with. Check out www.aspfaq.com/5006.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)