SQL: Create view using multiple tables and a cursor

I have two tables : ‘pc’ and ‘results’. the table ‘results’ contains so many results of every pc. I need to create view that contains all pc columns and the last result of every pc. I’ve tried this but it didn’t work.

select *
from pc,resultat where pc.code_pc=result.code_pc
order by code_resultat DESC limit 3

Do I have to use a cursor? if yes, how?

EDIT : PC ID_pc name x y 1 Station1 1 1 2 Station2 2 2

Result table:

code_rslt  ID_pc  parametre  value  date
1           1     ph         6      15/06/2015
2           2     ph         6.3    15/06/2015
3           1     ph         6.6    16/06/2015
4           2     ph         6.2    16/06/2015

I need a niew like this

ID_pc  name     x    y code_rslt  parametre  value  date
1      Station1 1    1     3        ph        6.6   16/06/2015
2      Station2 2    2     4        ph        6.2   16/06/2015

Answer

I think what you are looking for is something like this:

Select p.*,r.*
from pc p
    inner join 
    Results r 
    on p.ID_pc = r.ID_pc
Where r.Code_reslt = (Select MAX(code_rslt) from results where ID_pc = p.ID_PC)

Advertisement

Answer