Combining select statements 1

[dolodolo]

Hi,

I am trying to run a query that will first select a set of data based on one criteria and then select another set of data based on the results of that query.

Here's my attempt - I know I'm mising something - thanks for your help.

select
main.thmy,
t.uref uref,
t.upostdate postmonth,
a.scode acct,
p.scode property,
d.samount amt,
j.scode job,
t.snotes notes
FROM

(select
t.upostdate postmonth,
d.hprop prop,
d.hinvorrec thmy,
d.hacct acct
from trans t
inner join detail d on d.hinvorrec = t.hmy
inner join acct a on d.hacct = a.hmy
where
t.upostdate = '10/01/2009' and t.itype = 10
and a.scode between '11000' and '11399') main


trans t
inner join detail d on d.hinvorrec = t.hmy
Inner join acct a on d.hacct = a.hmy
Inner join property p on d.hprop = p.hmy
Left outer join job j on d.hjob = j.hmy
inner join main m on m.thmy = d.hinvorrec
and m.prop = d.hprop
and m.acct = d.hacct

 

[markros]

From the quick glance the query looks OK except that you're missing JOIN condition here

main ????? trans t

Should be select my fields from (Some complex query) MAIN inner join Trans T on Main.TransID = T.TransID...

(TransID field you would need to include into the complex query)

 

[dolodolo]

Thanks - I am still getting an error - Incorrect syntax near 'm'.

Any thoughts?

select
m.thmy,
t.hmy,
t.uref uref,
t.upostdate postmonth,
a.scode acct,
p.scode property,
d.samount amt,
j.scode job,
t.snotes notes
FROM

(select
t.upostdate postmonth,
d.hprop prop,
d.hinvorrec thmy,
d.hacct acct
from trans t
inner join detail d on d.hinvorrec = t.hmy
inner join acct a on d.hacct = a.hmy
where
t.upostdate = '08/01/2009' and t.itype = 10
and a.scode between '11000' and '11399') main m
inner join detail d on m.thmy = d.hinvorrec
and m.prop = d.hprop
and m.acct = d.hacct
inner join trans t on d.hinvorrec = t.hmy
Inner join acct a on d.hacct = a.hmy
Inner join property p on d.hprop = p.hmy
Left outer join job j on d.hjob = j.hmy

 

[markros]

You can not give two aliases for derived table.

Look close what you have now:

a.scode between '11000' and '11399') main m

 

[dolodolo]

Thanks, it works now!!!!