I am fetching data form two tables, tour and tour_price using left join, But from pricing table i am not able to fetch minimum price.
Here is my query
SELECT t1.id,t1.tour_title,t2.price
FROM tours t1
LEFT JOIN tour_dates t2 ON t1.id=t2.tour_id
WHERE t1.status = '1' AND ( t2.price between 40 AND 100 )
GROUP BY t2.tour_id
ORDER BY t2.price DESC
I think there is minor change i need, but couldn't figure out.
please provide some help. Thank you.
It should be:
SELECT t1.id, t1.tour_title, MIN(t2.price) min_price
FROM tours t1
LEFT JOIN tour_dates t2 ON t1.id = t2.tour_id AND t2.price BETWEEN 40 AND 100
WHERE t1.status = '1'
GROUP BY t1.id
ORDER BY min_price DESC
MIN(t2.price) to get the minimum price.LEFT JOIN, the conditions on the second table should be put in the ON clause. Otherwise, the tours that don't have any matching dates will be filtered out, because t2.price will be NULL.GROUP BY, because it will be NULL if there's no match. All the tours with no dates would then be grouped together as one result, which is not what you usually want.If you don't want the result to include tours with no dates, you should be using INNER JOIN rather than LEFT JOIN. If you're confused about the difference, see
Edit: This answer is for MS SQL and not for MySQL. Thanx Barmar for the comment.
Try to use the MIN() function on t2.price.
You also need to group by the values that you do a select on except for MIN() because it is a aggregate function.
SELECT t1.id,t1.tour_title, MIN(t2.price )
FROM tours t1
LEFT JOIN tour_dates t2 ON t1.id=t2.tour_id
WHERE t1.status = '1' AND ( t2.price between 40 AND 100 )
GROUP BY t1.id,t1.tour_title
ORDER BY MIN(t2.price ) DESC