MIN

G

Ghena

Hi, I know this query is very difficult if you have not the
relationship beetween all the table.

But I need to filter this table to obtain only one record choosen as
the minimum of "DIFF" field

live example:
id tipo id_sogg id_ogg prezzo_cat dispo set_richiesta max_pax min_pax diff totale_pax DISPREALE STATUS
29 tripla 3268 1040 € 218,00 0 Vero 3 2 1 0 0 rq
46 Quadrupla 3348 1040 € 444,00 35 Vero 4 2 2 27 8 ok


I need to display the min(diff) of both

id tipo id_sogg id_ogg prezzo_cat dispo set_richiesta max_pax min_pax diff totale_pax DISPREALE STATUS
29 tripla 3268 1040 € 218,00 0 Vero 3 2 1 0 0 rq

this results is the minimum DIFF (1)

how could I display filter by min(diff) and display all the columns?

This is the actual query.

SELECT sistemazioni_base.id, sistemazioni_base.tipo,
sistemazioni.id_sogg, sistemazioni.id_ogg, sistemazioni.prezzo_cat,
sistemazioni.dispo, sistemazioni_base.min_pax,
sistemazioni_base.max_pax, [max_pax]-2 AS diff, offerte.set_richiesta,
IIf(IsNull(Sommapax),0,Sommapax) AS totale_pax, dispo-totale_pax AS
DISPREALE, IIf(DISPREALE<=0,IIf(SET_RICHIESTA=True,'rq','ko'),'ok') AS
STATUS
FROM ((sistemazioni_base INNER JOIN sistemazioni ON
sistemazioni_base.id = sistemazioni.id_tipo) INNER JOIN offerte ON
sistemazioni_base.codoff = offerte.codoff) INNER JOIN somma_totpax ON
sistemazioni.id_sogg = somma_totpax.id_sogg
WHERE (((sistemazioni.id_ogg)=1040) AND
((sistemazioni_base.min_pax)<=2) AND ((sistemazioni_base.max_pax)>=2)
AND ((sistemazioni_base.id_strut)=11));
 
V

Vincent Johns

Yes, it would have been easier to understand your database structure if
you had posted sample records from each Table that would produce the
results from your Query.

Anyway, your Query's SQL appeared to have some mistakes (or at least my
Access complained), so I rewrote it as follows:

[Query1] SQL:

SELECT sistemazioni_base.id, sistemazioni_base.tipo,
sistemazioni.id_sogg, sistemazioni.id_ogg,
sistemazioni.prezzo_cat, sistemazioni.dispo,
offerte.set_richiesta, sistemazioni_base.min_pax,
sistemazioni_base.max_pax,
[sistemazioni_base].[max_pax]-2 AS diff,
IIf(IsNull(Sommapax),0,Sommapax) AS totale_pax,
[dispo]-[totale_pax] AS DISPREALE,
IIf([DISPREALE]<=0,IIf([offerte].[SET_RICHIESTA]
=True,'rq','ko'),'ok') AS STATUS
FROM (sistemazioni
INNER JOIN somma_totpax
ON sistemazioni.id_sogg = somma_totpax.id_sogg)
INNER JOIN (sistemazioni_base
INNER JOIN offerte
ON sistemazioni_base.codoff = offerte.codoff)
ON sistemazioni.id_tipo = sistemazioni_base.id
WHERE (((sistemazioni.id_ogg)=1040)
AND ((sistemazioni_base.min_pax)<=2)
AND ((sistemazioni_base.max_pax)>=2)
AND ((sistemazioni_base.id_strut)=11));

and it produced the following output:

[Query1] Query Datasheet View (folded to keep the lines short):

id tipo id_sogg id_ogg prezzo_cat dispo
-- ---- ------- ------ ---------- -----
29 tripla 3268 1040 € 218,00 0
46 Quadrupla 3348 1040 € 444,00 35

set_richiesta min_pax max_pax diff totale_pax
------------- ------- ------- ---- ----------
Vero 2 3 1 27
Vero 2 4 2 27

DISPREALE STATUS
--------- ------
-27 rq
8 ok

The following query, based on it, emits just one record:

[Q_MinDiff] SQL:

SELECT Q1.id, Q1.tipo, Q1.id_sogg, Q1.id_ogg,
Q1.prezzo_cat, Q1.dispo, Q1.set_richiesta,
Q1.min_pax, Q1.max_pax, Q1.diff, Q1.totale_pax,
Q1.DISPREALE, Q1.STATUS
FROM Query1 AS Q1
INNER JOIN
[SELECT Min(Query1.diff) AS MinOfDiff FROM Query1].
AS Q2 ON Q1.diff = Q2.MinOfDiff
ORDER BY Q1.id;

[Q_MinDiff] Query Datasheet View (split to keep lines short):

id tipo id_sogg id_ogg prezzo_cat dispo
-- ---- ------- ------ ---------- -----
29 tripla 3268 1040 € 218,00 0

set_richiesta min_pax max_pax diff totale_pax
------------- ------- ------- ---- ----------
Vero 2 3 1 27

DISPREALE STATUS
--------- ------
-27 rq

I hope this helps.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

Hi, I know this query is very difficult if you have not the
relationship beetween all the table.

But I need to filter this table to obtain only one record choosen as
the minimum of "DIFF" field

live example:
id tipo id_sogg id_ogg prezzo_cat dispo set_richiesta max_pax min_pax diff totale_pax DISPREALE STATUS
29 tripla 3268 1040 € 218,00 0 Vero 3 2 1 0 0 rq
46 Quadrupla 3348 1040 € 444,00 35 Vero 4 2 2 27 8 ok


I need to display the min(diff) of both

id tipo id_sogg id_ogg prezzo_cat dispo set_richiesta max_pax min_pax diff totale_pax DISPREALE STATUS
29 tripla 3268 1040 € 218,00 0 Vero 3 2 1 0 0 rq

this results is the minimum DIFF (1)

how could I display filter by min(diff) and display all the columns?

This is the actual query.

SELECT sistemazioni_base.id, sistemazioni_base.tipo,
sistemazioni.id_sogg, sistemazioni.id_ogg, sistemazioni.prezzo_cat,
sistemazioni.dispo, sistemazioni_base.min_pax,
sistemazioni_base.max_pax, [max_pax]-2 AS diff, offerte.set_richiesta,
IIf(IsNull(Sommapax),0,Sommapax) AS totale_pax, dispo-totale_pax AS
DISPREALE, IIf(DISPREALE<=0,IIf(SET_RICHIESTA=True,'rq','ko'),'ok') AS
STATUS
FROM ((sistemazioni_base INNER JOIN sistemazioni ON
sistemazioni_base.id = sistemazioni.id_tipo) INNER JOIN offerte ON
sistemazioni_base.codoff = offerte.codoff) INNER JOIN somma_totpax ON
sistemazioni.id_sogg = somma_totpax.id_sogg
WHERE (((sistemazioni.id_ogg)=1040) AND
((sistemazioni_base.min_pax)<=2) AND ((sistemazioni_base.max_pax)>=2)
AND ((sistemazioni_base.id_strut)=11));
Click to expand...
 
Top