1

I have two tables - Boxes and Items:

Boxes
Id|Name
 1|Box1
 2|Box2
 3|Box3

Items
Id|Size|Box
 1|2   |1
 2|3   |1
 3|2   |2
 4|5   |2

I need to get all items from Boxes joined with MAX(Size) from Items. If there is no corresponding Size from Items, Size should be 0.

The results should be like this:

Result
Id|Name|Size
 1|Box1|3
 2|Box2|5
 3|Box3|0

From what I've read there are numerous ways, like subqueries or using GROUP BY (which, apparently, isn't allowed in MSSQL). What is the most efficient method which can work across any (or at least) most RDBMS?

Technical
  • 145
  • 1
  • 11

3 Answers3

2
SELECT A.ID,A.Name,COALESCE(MAX(Size),0)
FROM Boxes AS A
LEFT JOIN ITEMS AS B
ON B.BOX = A.Id
GROUP BY A.ID,A.Name
LONG
  • 4,490
  • 2
  • 17
  • 35
  • I saw a similar solution on https://stackoverflow.com/questions/18694094/select-only-rows-by-join-tables-max-value. However, there are some warnings about using this method, – Technical Aug 10 '17 at 18:36
0

I don't have a way to test this out on MSSQL but, in my experience, subqueries are the most efficient way to do things like this.

select b.id, b.name, 
  (select max(i.size) from items i where i.box = b.id) as size 
from boxes b;
disperse
  • 1,216
  • 10
  • 22
0

Here are couple of solutions to the problem. @LONG's answer is the best:

declare @a table(
 ID INT,
 [Name] VARCHAR(20)
)
declare @b table(
 ID INT,
 Size INT,
 Box INT
)

INSERT @a VALUES(1,'Box 1'),(2,'Box 2'),(3, 'Box 3')
INSERT @b VALUES(1,2,1),(2,3,1),(3,2,2),(4,5,2)

select * from @a
select * from @b

select 
A.ID,
A.[Name],
ISNULL(BB.MaxSize, 0)
from @a A
LEFT JOIN (SELECT Max(Size) AS MaxSize, Box FROM @b B 
GROUP BY B.Box) BB ON A.ID = BB.Box


SELECT A.ID,A.Name,COALESCE(MAX(Size),0)
FROM @a AS A
LEFT JOIN @b AS B
ON B.Box = A.Id
GROUP BY A.ID,A.Name
Soham Bhattacharjee
  • 94
  • 6