How to find the first record which has no other version?-CodePudding

I have two tables Project and ID_CARD

PROJECT 
ID   PROJECT_ID
123  93773
245  98287
675  93889

ID_CARD

PROJECT_ID    CODE         Version    STATUS
245          98287001      1          DRAFT
245          98287002      2          ARCHIVED
245          98287003      3          ACTIVE
245          98287004      4          DRAFT
675          93889001      1          DRAFT
123          93773001      1          DRAFT

RESULT EXPECTED :

675          93889001      1          DRAFT

I need all the records from ID_CARD which have initial DRAFT version only

select * from id_card ic
left join project p on p.project_id= ic.project_id
where ic.status= 'DRAFT'

It fetches all the records but I want only the one who do have version 2. Please help

CodePudding user response：

You coul try uisng a subqury for count of status ) 1 and join wuth id_card

select * from id_card i
inner join  (
    select project_id, 
    from id_card 
    group by project_id
    having count(status) = 1
) a on a.project_id = i.project_id and i.status= 'DRAFT'

CodePudding user response：

An exists query should do it:

select *
from id_card as ic1
where ic1.status = 'draft' and not exist (
    select *
    from id_card as ic2
    where ic2.project_id = ic1.project_id
    and ic2.status <> 'draft'
)