JOIN Query that returns only the first record from every joined second table

Hi!

Here's my problem: I have two tables.

Table1:

recID       Description
a             record a
b             record b
c             record c
d             record d

Table2:
recID       Table1RecID       Description
1             a                       Record 1
2             a                       Record 2
3             a                       Record 3
4             b                       Record 4
5             b                       Record 5

I would like to make a left join query, that would return ONLY ONE (can be random) record for every TABLE1 record FROM TABLE 2. Example:

recIDTable1         RecIDTable2        DescTable2
a                         2                       Record2
b                         4                       Record4
LVL 1
coyoteeAsked:
Who is Participating?
 
OtanaCommented:
Try this:

select
*
from Table1 t1
left join (select Table1RecID, max(Description) from Table2 group by Table1RecID) t2 on t1.recID = t2.Table1RecID
0
 
HuyBDCommented:
Try this!

Select Top  1 Table1 .recID,   Table2.RecID,  Table2.Desc From Table1, Table2
order by newid() --this for random
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.