CODEWITHSUNDEEP
sqlsql-serverjoin
Kamilius
Kamilius

Reputation: 587

Return not all, but only first item of each joined table item

SELECT Id
FROM Container_A 
JOIN Container_B 
ON 
Container_A.Container_B_FK = Container_B.Id
ORDER BY Container_A.Id

This query return all elements of all Container_A items related to Container_B. The question is - How to get only first (with min Id) item related to each Container_B item.

Upvotes: 0

Views: 58

Answers (4)

Guffa
Guffa

Reputation: 700690

The problem with queries like this is that "first" has no meaning at all unless you specify what the order of the items are within the group. If you want the lowest item id, then you can group on the container b and use the min aggreagate to get the lowest item id from each group:

select
  b.Id,
  min(a.Id) as A_Id
from
  Container_B b
  inner join Container_A a on a.Container_B_FK = b.Id
group by
  b.Id
order by
  b.Id

Upvotes: 1

yovie
yovie

Reputation: 79

SELECT Id
FROM Container_A 
 JOIN Container_B 
   ON Container_A.Container_B_FK = Container_B.Id
 ORDER BY Container_A.Id ASC
 LIMIT 0,1

Upvotes: 1

SQB
SQB

Reputation: 4078

SELECT 
    MIN(a.Id) AS a_id,
    b.Id AS b_id
FROM Container_A a
JOIN Container_B b
ON (Container_A.Container_B_FK = Container_B.Id)
GROUP BY b.Id
ORDER BY a_Id

Upvotes: 1

vhadalgi
vhadalgi

Reputation: 7189

    SELECT top 1 *,min(Id) as minimum
    FROM Container_A 
    JOIN Container_B 
    ON 
    Container_A.Container_B_FK = Container_B.Id
group by Container_A.id,Container_B.id 
    ORDER BY minimum

Upvotes: 1

Related Questions