CODEWITHSUNDEEP
mysqlsqldatabase
jrm
jrm

Reputation: 925

How to SELECT FROM WHERE last record = x?

I have 2 different tables:
dossiers (id, name, disp)
dossiers_etat (id, id_dossier, open, incharge, date)

dossiers is very simple, it is a list of names with their id

dossiers_etat is linked to dossiers in a relation 1-N : 1 dossiers can be linked to N dossiers-etat, thanks to a join: dossiers.id = dossiers_etat.id_dossier

I use dossiers_etat as a timeline to record every change of state of my dossiers: 1 dossiers can be open="Y" today, but open="N" yesterday
I can find the last record concerning a dossiers thanks to dossiers_etat.date or dossiers_etat.id (last date and last id show the most recent record)

How can I complete this query to have a list of dossiers that are open="Y" and incharge="-" in their LAST record?

SELECT *
FROM dossiers
JOIN dossiers_etat
ON dossiers.id = dossiers_etat.id_dossier
WHERE dossiers.disp = "Y" AND dossiers_etat.open = "Y" AND dossiers_etat.incharge = "-"
ORDER BY dossiers.id DESC

Thank you very much for your help!

Upvotes: 2

Views: 422

Answers (3)

Shimu
Shimu

Reputation: 1147

Try this one:

    SELECT 
    *
FROM
    dossiers
        JOIN
    dossiers_etat ON dossiers.id = dossiers_etat.id_dossier
WHERE
    dossiers.disp = 'Y'
        AND dossiers_etat.open = 'Y'
        AND dossiers_etat.incharge = '-'
        AND dossiers_etat.id = (SELECT 
            *
        FROM
            dossiers_etat
        GROUP BY id_dossiers
        ORDER BY date DESC)
ORDER BY dossiers.id DESC;

With the subselect you will get the id's from your last entries from the dossiers table. And with the outer SELECT you will get all row's which opens the right entry and are also within the last records.

Upvotes: 1

M.Kreusburg
M.Kreusburg

Reputation: 19

try this

SELECT *
FROM dossiers
JOIN dossiers_etat
ON dossiers.id = dossiers_etat.id_dossier
WHERE dossiers.disp = "Y" AND dossiers_etat.open = "Y" AND dossiers_etat.incharge = "-"
ORDER BY dossiers.id DESC
limit 1

Upvotes: 1

Orangecrush
Orangecrush

Reputation: 1990

Try this:

SELECT *
FROM dossiers B
JOIN dossiers_etat as C
ON B.id = C.id_dossier
WHERE B.disp = "Y" AND C.open = "Y" AND C.incharge = "-"
AND C.date = (SELECT max(date) from dossiers_etat WHERE id_dossier = C.id_dossier)
ORDER BY B.id DESC

Upvotes: 1

Related Questions