SQL Inner Join Multiple Value

Question

Hello,

I have 2 tables like below :

Table 1

ID Name IP

1 SERVER-A 10.10.10.1

2 SERVER-B 10.10.10.2

Table 2

ID Mac Speed

1 0000.1111.2222 1000

1 0000.1111.3333 1000

2 0000.2222.2222 1000

When i join table1 to table 2 using ID, then the result for ID 1 is

ID Name IP Mac Speed

1 SERVER-A 10.10.10.1 0000.1111.2222 1000

1 SERVER-A 10.10.10.1 0000.1111.3333 1000

Can we modify so when joined table1 and table 2, if there multiple entry in table 2 we only pick one of the data so the final result is :

ID Name IP Mac Speed

1 SERVER-A 10.10.10.1 0000.1111.2222 1000

OR

ID Name IP Mac Speed

1 SERVER-A 10.10.10.1 0000.1111.3333 1000

Answer 1

There are several solutions. To use JOIN only, try this:

select t1.*, t2.Mac, t2.Speed
from Table1 t1
inner join Table2 t2 on t2.ID = t1.ID
left join Table2 x on x.ID = t2.ID and x.Mac < t2.Mac
where x.ID is null