Selezionare ultimo record di una sotto query

stefanoxjx

Utente Attivo
24 Feb 2017
46
1
8
56
Ciao a tutti,
ho un problema dal quale non riesco a venirne fuori nonostante svariate prove.
Ho una tabella "Anagrafica" e una tabella "Assunzioni" dalle quali riesco a ricavarmi i dati anagrafici del dipendente e se lo stesso è attualmente assunto o dimesso.
La tabella "Assunzioni" può avere più righe e nello specifico una per ogni nuovo contratto o assunzione.
Per esempio:
118/06/2019Tempo determinato18/07/2019
225/09/2020Tempo indeterminatoNULL

Quindi riesco a capire se il dipendente è ancora operativo o dimesso dal fatto che ci sia oppure non la data di dimissione nel record con id più alto.
A tale scopo ho preparato una query che sarebbe questa:


Codice:
SELECT * FROM Dipendenti d WHERE (Cognome LIKE '%%' OR Nome LIKE '%%')
AND Cooperative_Id = 1
AND if((SELECT max(id) From assunzioni a where a.dipendenti_id = d.id AND a.datadimissione IS not NULL LIMIT 1), 0, 1)
ORDER BY Cognome

ma la sotto query (quella interna alla if per capirci) funziona bene solo se nella tabella "Assunzioni" c'è solo una riga inerente il Dipendente.id, ma nel caso specificato sopra il record che ritorna è quello con la data di dimissione e quindi mi trovo con un risultato che non è quello aspettato.
Ho provato con GROUP BY, con ORDER BY, con LIMIT ma non riesco a fare in modo che mi ritorni il record corretto.
Chiedo quindi aiuto altrimenti ci perdo altri sei mesi :(
Grazie.
 

marino51

Utente Attivo
28 Feb 2013
3.203
207
63
Lombardia
la query, restituisce
datadimissione = null per i dipendenti attivi
oppure la data di chiusura del rapporto

1624287636416.png


se ti garba, questa é la query

SQL:
SELECT
  x.dipendenti_id
, CONVERT(varchar, x.dataassunzione, 23) AS dataassunzione
, d.cognome
, d.nome
, CONVERT(varchar, a.datadimissione, 23) AS datadimissione
FROM
(
SELECT
  dipendenti_id
, max(dataassunzione) AS dataassunzione
FROM assunzioni
GROUP BY dipendenti_id
) x
LEFT JOIN dipendenti d ON x.dipendenti_id = d.id
LEFT JOIN assunzioni a ON a.dipendenti_id = d.id AND a.dataassunzione = x.dataassunzione
WHERE ( d.cognome LIKE '%gno%' OR d.nome LIKE '%nom%' )
AND d.Cooperative_Id = 1
ORDER BY d.cognome, d.nome

ps, i "convert" mi sono serviti solo per formattare la data, puoi evitarli o usare quelli propri di mySQL
 
  • Like
Reactions: stefanoxjx

stefanoxjx

Utente Attivo
24 Feb 2017
46
1
8
56
Ti ringrazio per la query, ma purtroppo non funziona.
Direi di semplificare la cosa, altrimenti ho anche difficoltà a spiegare il risultato che mi servirebbe avere.
Ammettendo di avere una tabella come questa:
idDipendenti_iddata_assunzionedata_dimissione
1318/06/202031/12/2020
2620/06/2020NULL
3720/09/202031/12/2020
4430/09/202030/11/2020
5301/02/202130/06/2021
6301/09/2021NULL
7704/07/2021NULL
8918/07/2021NULL
91120/07/2021NULL

Guardando la colonna Dipendenti_id, i dipendenti che hanno uno storico sono quelli con id 3 e 7 e sono quelli che mi complicano la vita.
Dovrei tirare fuori una query che nel caso specifico sopra mi dia come risultato solo i dimessi alla data attuale, quindi nel caso specifico solo il dipendente con ID 4.
Questo perchè l'id 3 è stato assunto e dimesso due volte, ma alla riga 6 è stato assunto e attualmente lo è ancora, il 7 è stato assunto e dimesso alla riga 3 ma poi riassunto alla riga 7 e così via.
Spero di essermi spiegato.
Grazie.

Stefano
 

marino51

Utente Attivo
28 Feb 2013
3.203
207
63
Lombardia
Ti ringrazio per la query, ma purtroppo non funziona
potremmo forse dire che non l'hai capita,

con l'ultima tabella pubblicata la query estrae un risultato consono,
1624343416414.png


se l'avessi capita, avresti aggiunto
SQL:
AND a.datadimissione is not null
nella clausola where prima dell' "order by", ottenendo

1624343593621.png


cosi come togliendo il "NOT" ottieni tutti i dipendenti con contratto in essere
1624343823491.png


se cerchi qualcosa di più semplice, non sono riuscito a fare meglio,
forse qualcuno più capace può suggerirti una query "unica" più semplice
 
  • Like
Reactions: stefanoxjx

marino51

Utente Attivo
28 Feb 2013
3.203
207
63
Lombardia
considera anche il formato delle date che usi,

nel db dovrebbero essere dichiarate "date" o "datetime"

poi,
la data 01/02/2021 in "lingua" americana esprime 2 gennaio 2021

la stessa data 01/02/2021 in "lingua" italiana esprime 1 febbraio 2021

quindi come stai usando le date ?
 
  • Like
Reactions: stefanoxjx

marino51

Utente Attivo
28 Feb 2013
3.203
207
63
Lombardia
d' altra parte non v'é ombra di dubbio che la query più interna estragga la data più alta per ciascun dipendente

1624348928301.png


la query lavora soprattutto in modo consistente, estraendo le date più alte NON l' ID del record perchè
i contratti potrebbero essere inseriti in ordine casuale e quindi l'ID può non esprime l'ultimo inserimento "necessario"
quello con data più alta

ps la chiocciola che precede il nome della tabella, identifica una tabella temporanea, usata per la prova della query
 
Ultima modifica:
  • Like
Reactions: stefanoxjx

stefanoxjx

Utente Attivo
24 Feb 2017
46
1
8
56
Non c'è ombra di dubbio sul fatto che non abbia capito bene la query, non sono ancora arrivato a questi livelli di complessità.
Comunque ci sono delle cose che non mi tornano.
Come prima cosa ho dovuto modificare la query come segue:
Codice:
SELECT x.dipendenti_id, x.dataassunzione AS dataassunzione, d.cognome, d.nome, a.datadimissione AS datadimissione
FROM
(SELECT dipendenti_id, max(dataassunzione) AS dataassunzione FROM assunzioni GROUP BY dipendenti_id) x
LEFT JOIN dipendenti d ON x.dipendenti_id = d.id
LEFT JOIN assunzioni a ON a.dipendenti_id = d.id AND a.dataassunzione = x.dataassunzione
WHERE (d.cognome LIKE '%%' OR d.nome LIKE '%%')
AND d.Cooperative_Id = 1 AND a.datadimissione is null
ORDER BY d.cognome, d.nome

per quanto riguarda le date, le gestisco come MySQL vuole e i campi dataassunzione e datadimissione sono di tipo date.
Nella tabella che ho postato in precedenza, avevo messo un formato più semplice da leggere per noi umani :)

Ho allegato a questo post la tabella sulla quale sto facendo le prove, così magari perdi meno tempo anche tu a verificare.
Eseguendo la query nella tabella allegata, con il IS NOT NULL dovrei avere come risultato i Dipendenti_id: 3, 185, 275, 292.
Invece ho come risultato solo 3 e 185.

Se invece eseguo la query con IS NULL, ho come risultato 1 e 32 e mancano tutti gli altri che hanno data dimissione == NULL.
Inoltre, ma qui devo dire che sono io a non averlo specificato, può esserci il caso in cui sulla tabella Dipendenti ci siano dei dati anagrafici e che però sulla tabella Assunzioni non sia ancora stato inserito nulla inerente ad un determinato dipendente.
In questo caso avrei bisogno che nella lista apparissero anche queste anagrafiche, sia con IS NOT NULL che con IS NULL.

Per questo ti avevo detto che forse era meglio semplificare, perchè vorrei evitare di farti perdere un sacco di tempo.
Grazie.
 

Allegati

  • Assunzioni.zip
    614 bytes · Visite: 158

marino51

Utente Attivo
28 Feb 2013
3.203
207
63
Lombardia
tabella Dipendenti ci siano dei dati anagrafici e che però sulla tabella Assunzioni non sia ancora stato inserito nulla inerente ad un determinato dipendente.
ok questo lo avevo pensato, per ottenere questo é necessario aggiungere in "UNION" una seconda query che estrae i dipendenti non esistenti in assunzioni

ho aggiunto una anagrafica e testato la qury relativa che sembra funzionare

1624456587935.png


bisogna però sapere quali dati estrarre perchè questa query deve "combaciare" in termini di colonne e tipo dati alla precedente

da ultimo,
Per questo ti avevo detto che forse era meglio semplificare

credo sia il modo più semplice possibile


ora controllo la query da te modificata per cercare di capire come mai perde dei record

poi integro la nuova query nella precedente e tutto dovrebbe essere fatto
 
  • Like
Reactions: stefanoxjx

marino51

Utente Attivo
28 Feb 2013
3.203
207
63
Lombardia
rivista in base alle ultime considerazioni, ti posto la query ed un suo risultato (where non commentata)
ho esposto la colonna "cooperative_id" perché necessaria per una coerenza dell'insieme

SQL:
SELECT * FROM (
SELECT x.dipendenti_id, d.Cooperative_Id, x.dataassunzione, d.cognome, d.nome, a.datadimissione
FROM
( SELECT dipendenti_id, max(dataassunzione) AS dataassunzione FROM assunzioni GROUP BY dipendenti_id ) x
LEFT JOIN dipendenti d ON x.dipendenti_id = d.id
LEFT JOIN assunzioni a ON a.dipendenti_id = d.id AND a.dataassunzione = x.dataassunzione
WHERE a.datadimissione is not null
-- WHERE a.datadimissione is null
UNION ALL
SELECT d.id AS dipendenti_id, d.Cooperative_Id, null AS dataassunzione, d.cognome, d.nome, null AS datadimissione
FROM dipendenti d
LEFT JOIN assunzioni a ON d.id = a.dipendenti_id
WHERE a.dipendenti_id is null
) y
WHERE ( cognome LIKE '%%' OR nome LIKE '%%' )
AND Cooperative_Id = 1
ORDER BY cognome, nome

1624459548509.png


ti trovi ?
 
  • Like
Reactions: stefanoxjx

stefanoxjx

Utente Attivo
24 Feb 2017
46
1
8
56
Scusa se non ti ho ancora risposto, ma ho dei comportamenti diversi da quelli che dici e sto cercando di capire cosa sta succedendo.
Che possa dipendere dal fatto che uso MariaDB e non MySQL e sto dando troppo per scontato che sono la stessa cosa?
 

marino51

Utente Attivo
28 Feb 2013
3.203
207
63
Lombardia
la query che ti ho suggerito é abbastanza normale per funzionare con qualunque tipo di database,

ho capito che vengono esclusi dalla selezione alcuni elementi,
hai controllato i valori che vengono filtrati dalla
SQL:
AND Cooperative_Id = 1
può essere che non tutti abbiano valore 1 ?

come secondo step, controlla che
SQL:
SELECT dipendenti_id, max(dataassunzione) AS dataassunzione FROM assunzioni GROUP BY dipendenti_id
estragga i record correttamente, ovvero le date siano definite correttamente

il terzo step estrae elementi combinati dalle 3 tabelle (temporanea vedi step 2 + dipendenti + assunzioni
SQL:
SELECT x.dipendenti_id, d.Cooperative_Id, x.dataassunzione, d.cognome, d.nome, a.datadimissione
FROM
( SELECT dipendenti_id, max(dataassunzione) AS dataassunzione FROM assunzioni GROUP BY dipendenti_id ) x
LEFT JOIN dipendenti d ON x.dipendenti_id = d.id
LEFT JOIN assunzioni a ON a.dipendenti_id = d.id AND a.dataassunzione = x.dataassunzione
WHERE a.datadimissione is not null
-- WHERE a.datadimissione is null

il quarto step aggiunge le anagrafiche senza assunzioni
SQL:
SELECT d.id AS dipendenti_id, d.Cooperative_Id, null AS dataassunzione, d.cognome, d.nome, null AS datadimissione
FROM dipendenti d
LEFT JOIN assunzioni a ON d.id = a.dipendenti_id
WHERE a.dipendenti_id is null

ed infine l'ultimo step, unisce, filtra e ordina
SQL:
SELECT * FROM (
....
UNION ALL
....
) y
WHERE ( cognome LIKE '%%' OR nome LIKE '%%' )
AND Cooperative_Id = 1
ORDER BY cognome, nome

fai sapere
 
  • Like
Reactions: stefanoxjx

stefanoxjx

Utente Attivo
24 Feb 2017
46
1
8
56
Il problema sta in questa query secondo me, ed proprio quella su cui stavo lavorando per capire le differenze tra i tuoi e i miei risultati:
Codice:
SELECT dipendenti_id, max(dataassunzione) AS dataassunzione FROM assunzioni GROUP BY dipendenti_id

infatti, espandendola un po':
Codice:
SELECT id, dipendenti_id, dataassunzione, datadimissione, max(dataassunzione) AS dataassunzione FROM assunzioni GROUP BY dipendenti_id

trovo che mi da come risultato anche Dipendenti_id=1 che in realtà non dovrebbe esserci:
raggruppamento.PNG


perchè la tabella contiene il record più recente con Dipendenti_id=1 che ha data dimissione vuota (record 44):
tabella.PNG


Inoltre, se guardiamo il risultato della query, vediamo che gli id dei record estratti di Dipendenti_id 1 e 3 sono 40 e 43 che non equivalgono al record più recente.
Dovrebbero infatti essere 41 per Dipendenti_Id = 3 e 44 per Dipendenti_id = 1.
Tu hai gli stessi risultati oppure diversi?
Grazie.
 

marino51

Utente Attivo
28 Feb 2013
3.203
207
63
Lombardia
infatti, espandendola un po
non puoi espanderla un po', perchè cambi le condizioni, estrai più records di quello che deve essere estratto

puoi estendere la query più esterna, rispettando,
d.nomecolonna per tutti i campi che provengono dalla tabella dipendenti
a.nomecolonna per tutti i campi che provengono dalla tabella assunzioni
( "x." non può avere altre colonne )

le colonne che aggiungi devi riportarle anche nella query che ti estrae i dipendenti senza assunzioni

nella lista hai indicato id, suppongo sia l' id in assunzioni, dovresti scrivere così,
SQL:
SELECT
  x.dipendenti_id, x.dataassunzione,
  d.Cooperative_Id, d.cognome, d.nome,
  a.datadimissione, a.id
FROM
( SELECT dipendenti_id, max(dataassunzione) AS dataassunzione FROM assunzioni GROUP BY dipendenti_id ) x

mentre nella query che aggiunge i dipendenti senza assunzioni devi aggiungere id,
PHP:
SELECT d.id AS dipendenti_id, d.Cooperative_Id, null AS dataassunzione, d.cognome, d.nome, null AS datadimissione, null AS id
FROM dipendenti d
LEFT JOIN assunzioni a ON d.id = a.dipendenti_id
WHERE a.dipendenti_id is null

tutto questo se vuoi ottenere la lista di chi é assunto / non assunto / senza assunzioni (presente sempre nei casi precedenti)

se vuoi una lista diversa anche la query (più o meno complessa) sarà diversa


aggiungo un particolare, quando in una select é presente il "GROUP BY", tutte le colonne non "raggruppate" devono essere elencate nella clausola stessa
PHP:
GROUP BY dipendenti_id, id ) x
non elencarli costituisce errore di sintassi, mySQL mi sembra che non lo rilevi, ma lo tratta come elemento per cui eseguire il raggruppamento

esaminiamo il caso seguente, é normale che estragga 2 records se aggiungi id, essendo id diverso nei 2 records per il dipendente 3

1624829890194.png
 
Ultima modifica:
  • Like
Reactions: stefanoxjx

marino51

Utente Attivo
28 Feb 2013
3.203
207
63
Lombardia
ho dimenticato, questo il risultato xon "id" aggiunto

1624857501527.png



( pensiero : da ultimo si può agganciare anche la tabella cooperative per estrarne la descrizione )
 
  • Like
Reactions: stefanoxjx

marino51

Utente Attivo
28 Feb 2013
3.203
207
63
Lombardia
dovrebbe essere completa,

1624859479227.png


la query é scritta per renderla il più comprensibile possibile,

SQL:
SELECT
  y.dipendenti_id
, y.cognome
, y.nome
, y.Cooperative_Id
, c.cooperativa
, y.assunzioni_id
, y.dataassunzione
, y.datadimissione
FROM (
SELECT
  x.dipendenti_id
, d.cognome
, d.nome
, d.Cooperative_Id
, a.id                                   AS assunzioni_id
, CONVERT(varchar, x.dataassunzione, 23) AS dataassunzione
, CONVERT(varchar, a.datadimissione, 23) AS datadimissione
FROM
( SELECT dipendenti_id, max(dataassunzione) AS dataassunzione FROM assunzioni GROUP BY dipendenti_id ) x
LEFT JOIN dipendenti d ON x.dipendenti_id = d.id
LEFT JOIN assunzioni a ON a.dipendenti_id = d.id AND a.dataassunzione = x.dataassunzione
WHERE a.datadimissione is not null
-- WHERE a.datadimissione is null
UNION ALL
SELECT
  d.id  AS dipendenti_id
, d.cognome
, d.nome
, d.Cooperative_Id
, null  AS assunzioni_id
, null  AS dataassunzione
, null  AS datadimissione
FROM dipendenti d
LEFT JOIN assunzioni a ON d.id = a.dipendenti_id
WHERE a.dipendenti_id is null
) y
LEFT JOIN cooperative c ON y.cooperative_id = c.id
WHERE ( cognome LIKE '%%' OR nome LIKE '%%' )
AND Cooperative_Id = 1
ORDER BY cognome, nome

ok ?
 
  • Like
Reactions: stefanoxjx

marino51

Utente Attivo
28 Feb 2013
3.203
207
63
Lombardia
sono riuscito a creare una nuova query, che sembra essere più semplice,

una select combina le 3 tabelle, dipendenti, cooperative assunzioni ed il solo valore massimo della "dataassunzione" associato ad ogni record estratto

ho dovuto usare gli alias perché nelle tabelle vi sono molti nomi uguali

l'elemento caratteristico sta nella scelta degli elementi da estrarre
SQL:
WHERE ( a_dipendenti_id  is null OR ( a_datadimissione is not null AND x_dataassunzione = a_dataassunzione ) )
--WHERE ( a_dipendenti_id  is null OR ( a_datadimissione is null AND x_dataassunzione = a_dataassunzione ) )

puoi usare la "select *" per elencare le colonne che vuoi, nell'ordine necessario
ovviamente devono essere elencate anche nella select più interna

questo il (mio) risultato

1624882105366.png


questo il codice

SQL:
SELECT *
FROM (
    SELECT
      d.id             AS d_id
    , d.cognome        AS d_cognome
    , d.nome           AS d_nome
    , d.Cooperative_Id AS d_Cooperative_Id
    , c.id             AS c_id
    , c.cooperativa    AS c_cooperativa 
    , a.id             AS a_id
    , a.dipendenti_id  AS a_dipendenti_id
    , a.tipo           AS a_tipo
    , a.dataassunzione AS a_dataassunzione
    , a.datadimissione AS a_datadimissione
    , ( SELECT max(x.dataassunzione) FROM @assunzioni x WHERE x.dipendenti_id = d.id ) AS x_dataassunzione
    FROM      @dipendenti  d
    LEFT JOIN @cooperative c ON d.cooperative_id = c.id
    LEFT JOIN @assunzioni  a ON a.dipendenti_id  = d.id
) y
WHERE ( a_dipendenti_id  is null OR ( a_datadimissione is not null AND x_dataassunzione = a_dataassunzione ) )
--WHERE ( a_dipendenti_id  is null OR ( a_datadimissione is null AND x_dataassunzione = a_dataassunzione ) )
  AND ( d_cognome LIKE '%%' OR d_nome LIKE '%%' )
  AND d_Cooperative_Id = 1
ORDER BY d_cognome, d_nome


fai sapere
 
  • Like
Reactions: stefanoxjx
Discussioni simili
Autore Titolo Forum Risposte Data
S [PHP] Selezionare ultimo campo ed escludere doppioni PHP 16
B Selezionare un'immagine e salvare la selezione Photoshop 3
M Come selezionare e deselezionare radiobutton con jquery jQuery 1
Y Vorrei selezionare un colore di sfondo per una pagina web Javascript 0
S Upload file senza doverlo selezionare PHP 2
S Selezionare e deselezionare checkbox Javascript 0
M [PHP] Selezionare immagini directory non presenti in db PHP 11
S [PHP] selezionare colonna tabella utilizzando un array PHP 1
S [PHP] Selezionare i campi non in comune con le altre colonne della tabella PHP 4
L [PHP] CHECKBOX DA SELEZIONARE IN UNA TABELLA PHP 4
S [PHP] selezionare colonne in comune con un altra tabella PHP 5
T selezionare terza colonna della relatica row Database 2
G [Javascript] Selezionare una checkbox in maniera condizionata Javascript 5
G [PHP] Selezionare OGGI anno precedente per query sql PHP 2
A [RISOLTO] PHP Selezionare tutti i file con stessa estensione PHP 2
otto9due Selezionare singoli giorni su datepiker jQuery 1
S [PHP] Selezionare dati in intervallo di tempo PHP 21
gandalf1959 Selezionare un intervallo tra due date, php e mysql PHP 2
M [PHP] selezionare dati da db e fare confronti PHP 0
L [PHP] selezionare righe di una tabella con le checkbox e cancellarle PHP 2
R [Photoshop] Selezionare elemento in immagine ed esportarlo Photoshop 3
S [PHP] Selezionare tutte le colonne meno due PHP 6
asevenx Selezionare elementi da un elenco in modo dinamico e inviarli al database PHP 0
C selezionare distinti id e sommare uguali PHP 1
asevenx Realizzare modulo per selezionare elementi da una lista PHP 1
M Selezionare un risultato ogni 10 da select MySQL 4
L [php e mysql] selezionare sia i selezionati e i non selezionati PHP 1
M Selezionare un record da una tabella continua MS Access 0
H Selezionare la versione di Access Runtime in base all' applicazione MS Access 0
H Selezionare la versione di Access Runtime in base all' applicazione MS Access 0
F Selezionare voce di menu attiva HTML e CSS 1
asevenx selezionare prossima data da database PHP 2
T selezionare un campo da un db con radio button PHP 0
M Tabella PHP con sorgente dati csv: selezionare le riche e compilare Form eMail PHP 1
P Impossibile selezionare il database PHP 7
P impossibile selezionare il database PHP 0
felino Selezionare le colonne con una certa condizione PHP 2
S Selezionare files con drag and drop prima di upload PHP 0
F selezionare puntatore google engine Javascript 0
L Selezionare query da menu a tendina MS Access 2
M Selezionare quantità spostando una "barra" Javascript 5
M Selezionare dati tra due tabelle Database 3
M Selezionare dati tra due tabelle PHP 1
F selezionare dati tra due tabelle e prendere max valori PHP 10
M Selezionare un campo alla volta su un ciclo while PHP 47
T selezionare dati da mysql mediante form PHP 12
D selezionare solo dati non presenti in entrambe le tabelle PHP 5
S Selezionare campi da una tabella,vincolati da un'altra tabella Database 4
neo996sps [FORM] selezionare solo file con estensione MP3 HTML e CSS 2
D Selezionare tutti i checkbox di un form con jQuery jQuery 1

Discussioni simili