I'm using the following query, to select the lowest price in the database, along with all the additional data needed.
SELECT
p1.id as pid, p1.persons, p1.skipas_incl, temp.*
FROM
winter_accommodaties_prijzen p1
INNER JOIN (
SELECT
a.id as accom_id, p.vertrekdatum, Min(p.prijs) as prijs, a. max_personen, a.naam as naam, g.pistes_groen+g.pistes_blauw+g.pistes_rood+g.pistes_zwart as pistes, liften_type1+liften_type2+liften_type3+liften_type4 as liften, g.naam as gebied, g.lid, d.gid, d.naam as dorp, d.hoogte_dorp, d.afstand_utrecht, d.dalafdaling, g.min_hoogte, g.max_hoogte, t.naam as typenaam, afstand_piste
FROM
winter_accommodaties_prijzen p
INNER JOIN winter_accommodaties a ON (a.id = p.accid)
INNER JOIN winter_dorpen d ON (d.id = a.did)
INNER JOIN winter_gebied g ON (g.id = d.gid)
INNER JOIN winter_accommodaties_types t ON (a.tid = t.id)
WHERE
g.lid IN(1,2) AND (p.vertrekdatum BETWEEN '2016-08-30' AND '2017-06-30' OR p.vertrekdatum = '0000-00-00') AND g.max_hoogte >= 500 AND (g.pistes_groen+g.pistes_blauw+g.pistes_rood+g.pistes_zwart) >= 50 AND d.hoogte_dorp >= 500 AND d.afstand_utrecht <= 2500 AND prijs < 1200 AND p.persons >= 4
GROUP BY
p.accid
ORDER BY
prijs, naam ASC
LIMIT
0, 10) AS temp
ON
temp.accom_id = p1.accid AND
temp.prijs = p1.prijs AND
temp.vertrekdatum = p1.vertrekdatum
GROUP BY
p1.accid
The SELECT between brackets selects the various fields needed and the lowest price. The 'vertrekdatum' which is a date used in the parent query to get the correct price_id. This as a price/accommodation can have multiple leave dates, hence the combination between
price<->ID<->date
The problem currently is, is that the date selected (and possible other fields also), are not perse linked to the row with the MIN(p.prijs), resulting in an error in the parent query e.g. it only shows a selection on which it accidentally goes right.
I'm struggling on how to resolve this issue, as I have tried various options/linkages between tables, but don't get the expected outcome. Could you guys help me on a suggestion how to improve this query so it provides the expected outcome?
Solution
After some testing, playing around and lots of searching, the following is the outcome which works for me:
SELECT
subset.*, a.max_personen, a.naam as naam
FROM
(
SELECT
temp.*,
@rowNum := if(@prevValue = temp.accom_id, @rowNum+1, 1) as row_num,
@prevValue := temp.accom_id as accid1
FROM
(
SELECT
p.id as pid, p.accid as accom_id, p.prijs, p.vertrekdatum, p.persons, p.skipas_incl, g.pistes_groen+g.pistes_blauw+g.pistes_rood+g.pistes_zwart as pistes, g.liften_type1+g.liften_type2+g.liften_type3+g.liften_type4 as liften, g.naam as gebied, g.lid, d.gid, d.naam as dorp, d.hoogte_dorp, d.afstand_utrecht, d.dalafdaling, g.min_hoogte, g.max_hoogte, t.naam as typenaam, a.afstand_piste
FROM
winter_accommodaties_prijzen p
INNER JOIN winter_accommodaties a ON (a.id = p.accid)
INNER JOIN winter_dorpen d ON (d.id = a.did)
INNER JOIN winter_gebied g ON (g.id = d.gid)
INNER JOIN winter_accommodaties_types t ON (a.tid = t.id)
WHERE
g.lid IN(1,2)
AND (p.vertrekdatum BETWEEN '2016-09-16' AND '2017-05-16' OR p.vertrekdatum = '0000-00-00')
AND g.max_hoogte >= 500
AND (g.pistes_groen+g.pistes_blauw+g.pistes_rood+g.pistes_zwart) >= 50
AND d.hoogte_dorp >= 500
AND d.afstand_utrecht <= 2500
AND prijs < 1200
AND p.persons >= 4
ORDER BY
p.accid, p.prijs ASC, p.vertrekdatum) temp,
(SELECT @rowNum := 1 FROM DUAL) X,
(SELECT @prevValue := -1 FROM DUAL) Y) subset
INNER JOIN winter_accommodaties a ON (a.id = subset.accom_id)
WHERE
subset.row_num = 1
ORDER BY
prijs, naam ASC
LIMIT
0, 10
