-1

I have 2 tables, One table has status and another one table has mark and id.

table1

id versin name
1 0 ram
2 1 ram
3 2 ram
4 3 ram

table2

sid status name
1 Pass ram
2 Pass ram
3 Pass ram
4 Fail ram

this is my query,

select id, 
       case when status ='fail' or status='N/A' 
            then max(versin)-1
            when status = 'PASS' 
            then max(versin)
            else max(versin) 
            end as versin
from table1 A
inner join ( select max(versin) as mxversin 
             from table1 
             group by name ) B
    on B.id = A.id and B.mxversin = A.versin
left join table2 C 
    on C.name= B.name

In this scenario, I got correct versin, but the id is not matching.

I got,

table

id versin name
1 2 ram

I need

The result table should be

id versin name
3 2 ram

Please help me out from this scenario, thanks in advance

Akina
  • 39,301
  • 5
  • 14
  • 25
Vijay
  • 1
  • 1
    Your query returns an error indicating `B.id` is unknown, as there is no `id` column defined in the subquery that formed B. – blabla_bingo Apr 19 '22 at 02:46
  • Describe the task. What do you want to achieve? – Akina Apr 19 '22 at 04:28
  • Actually, I need data like e.g One user creates 4 versions of data like 0,1,2,3, And 0 to 2 versions are approved and 3 is in pending status. I need to show only the approved status of the maximum version. with the corresponding id of the version. So I prepared case when table2.status = pending then max(table1.version)-1 when table2.status = approved then max(table1.version) At this stage, I could not get the corresponding id of the versions, I got 0 version's id and max(version) data. Thanks in advance @Akina – Vijay Apr 19 '22 at 20:58
  • 1) Add these explanations into the question (see "Edit" link under it). 2) Your question is not accurate. Does the column `sid` in `table2` is one which you refer on as `id`? – Akina Apr 20 '22 at 04:22
  • Answer should come from table 1 - Id, version, name. And we can't use desc limit 1, because name column have various value like ram, vjy, akin like that. Second table Sid just auto increment id, matching with name only. – Vijay Apr 20 '22 at 07:35

1 Answers1

0

Test this:

SELECT t1.*
FROM table1 t1
JOIN table2 t2 ON t1.id = t2.sid AND t1.name = t2.name
WHERE t2.status = 'Pass'
ORDER BY t1.versin DESC LIMIT 1

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=e5c80834ad2662a47254c777adba1355

Akina
  • 39,301
  • 5
  • 14
  • 25
  • Answer should come from table 1 - Id, version, name. And we can't use desc limit 1, because name column have various value like ram, vjy, akin like that. Second table Sid just auto increment id, matching with name only. – Vijay Apr 20 '22 at 07:38
  • @Vijay You have told now that the model provided in the question is not adequate. Fix this and create adequate model which contains everything mentioned in your comment. Create a fiddle with source data, provide the link to it and show desired output for it with detailed explanations. – Akina Apr 20 '22 at 07:52