Select min value from another table with other column

Question

I have two tables; the first table is for the names of the main halls. It is called the halls.

id	Name
1	TA
2	TB

The second table is for indoor halls, named Halls_Interior, for each main hall, and contains the name of the inner hall, the starting date of the price period, its end date, and the price of the period.

id	IdHalls	Name	FromDate	EndDate	Price	(comment)
1	1	price1	2020-01-01	2020-12-01	500
2	1	price2	2020-01-01	2020-12-01	200	this is min price
3	2	price3	2022-01-01	2022-12-01	80
4	2	price4	2022-01-01	2022-12-01	50	this is min price

What is required is to display all the main halls from the first table and put a field with the name of the smallest price, the date of the beginning and the date of the end of the period, so that the form of the query becomes as follows:

NameHalls	NameHallsInterior	PriceName	FromDate	EndDate
TA	price2	200	2020-01-01	2020-12-01
TB	price4	50	2022-01-01	2022-12-01

As you can see, only two halls appeared, and each hall has the details of the lowest price found, meaning that I just want the lowest price data and put it next to each hall.

Here is the query that I reached, but unfortunately it will show all the internal halls, and I only want data on the lowest price for each main hall

SELECT 
    HallsInterior.NameHalls, HallsInterior.NameHallsInterior,  
    HallsInterior.FromDate, HallsInterior.EndDate, HallsInterior.Price
FROM     
    Halls 
INNER JOIN
    DataHall ON Halls.Id = DataHall.HallId

score 2 · Answer 1 · answered Apr 22 '23 at 02:42

2

Try this

SELECT H.Name, D.PriceName, D.FromDate, D.EndDate, D.Price
FROM     Halls H INNER JOIN
(SELECT IdHalls, Name as PriceName, FromDate, EndDate, Price, 
ROW_NUM() OVER(PARTITION BY IdHalls ORDER BY PRICE) as nr 
FROM Halls_Interior ) D ON H.Id = D.IdHalls
where D.nr = 1

answered Apr 22 '23 at 02:42

Besi

330
3
8

Thank you very much for the quick reply, and for the most wonderful solution, but is the query with use ROW_NUMBER() will be slow? – EBRAHEM Apr 22 '23 at 02:55
@EBRAHEM Not necessarily. Depends on the cardinality of the results vs the whole table. Eg small percent of the joined table then `OUTER APPLY` will be faster, large percent or if it's not well-indexed then `ROW_NUMBER` will be faster, – Charlieface Apr 23 '23 at 01:30

score 2 · Accepted Answer · answered Apr 22 '23 at 04:41

2

You can also achieve this using OUTER APPLY

SELECT a.Name,
    b.PriceName,
    b.FromDate,
    b.EndDate,
    b.Price
FROM Halls a
OUTER APPLY (
    SELECT TOP 1 x.IdHalls,
        x.[Name] as PriceName,
        x.FromDate,
        x.EndDate,
        x.Price 
    FROM Halls_Interior x
    WHERE x.IdHalls = a.Id
    ORDER BY x.Price
) b

answered Apr 22 '23 at 04:41

Juan Midence

36
3

Thank you very much for the help, the query was much clearer for your solution you put forward – EBRAHEM Apr 22 '23 at 19:10

Select min value from another table with other column

2 Answers2