Hello,

114078 1 QUAL
114078 2 GRAPH
114078 3 PROOF
114078 4 MAIL
114078 5 COMP
114078 6 CHANGE
114078 7 GRAPH
114078 8 COMP

the above data thats what i have in my cascadeadstatus table

select table1.bookcode,table1.adnumber,table1.seq,table1. status,cascadeadstatus.adnumber,cascadeadstatus.se q,cascadeadstatus.status
from(SELECT contractrowid,c.BookCode,entrydate,cas.AdNumber,Se q,Status,c.BookYear,DateSet
FROM book, bookyear, contract c, cascadeadstatus cas, ContractLineItem cli
WHERE book.BookRowID = bookyear.BookRef
and cas.AdNumber = cli.AdId
AND cli.ContractRef = c.ContractRowID
and bookyear.BookRef = c.BookRef
AND bookyear.BookYear = c.BookYear
and book.bookcode in ("CC")
and publicationdate between current_date and DATEADD(mm,12,current_date)
and status="COMP") table1, cascadeadstatus
where cascadeadstatus.adnumber="114078" and
cascadeadstatus.adnumber = table1.adnumber
and cascadeadstatus.seq = CASE
when cascadeadstatus.seq = table1.seq-1 and cascadeadstatus.status = "GRAPH" then table1.seq-1
when cascadeadstatus.seq = table1.seq-2 and cascadeadstatus.status = "GRAPH" then table1.seq-2
when cascadeadstatus.seq = table1.seq-3 and cascadeadstatus.status = "GRAPH" then table1.seq-3
when cascadeadstatus.seq = table1.seq-4 and cascadeadstatus.status = "GRAPH" then table1.seq-4
when cascadeadstatus.seq = table1.seq-5 and cascadeadstatus.status = "GRAPH" then
table1.seq-5
when cascadeadstatus.seq = table1.seq-6 and cascadeadstatus.status = "GRAPH" then
table1.seq-6
when cascadeadstatus.seq = table1.seq-7 and cascadeadstatus.status = "GRAPH" then
table1.seq-7
when cascadeadstatus.seq = table1.seq-8 and cascadeadstatus.status = "GRAPH" then
table1.seq-8
when cascadeadstatus.seq = table1.seq-9 and cascadeadstatus.status = "GRAPH" then
table1.seq-9
when cascadeadstatus.seq = table1.seq-10 and cascadeadstatus.status = "GRAPH" then
table1.seq-10
end

what i have in table1 is:

114078 5 COMP
114078 8 COMP

I want to get the following by joining the 2 tables: table1 and cascadeadstatus:

114078 5 COMP 2 GRAPH
114078 8 COMP 7 GRAPH


but i am getting:

114078 5 COMP 2 GRAPH
114078 8 COMP 2 GRAPH
114078 8 COMP 7 GRAPH

please help

Thanks

That looks complicated.
In human language (more or less), are you looking for the max(cascadeadstatus.seq) with cascadeadstatus.status = 'GRAPH' and cascadeadstatus.seq <= table1.seq ?

Argh! Stupid session died with all the work i prepared!

IVON,
Yes it is a complicated query. yesterday finally after 2-3 days I was able to get what I want. and Thank you so much for trying to help me.

Is it possible for you to post your solution so that we can all analyse it and learn