CODEWITHSUNDEEP
sqljoinunion
PureBluff
PureBluff

Reputation: 29

SQL - Union all / Inner join (with where clauses)

I'm trying to port a lot of old AS400 queries to SQL as I'm developing a GUI for reporting & updating records from the libraries in AS/400.

With that, I've encountered a query that has quite a few steps and the joining of tables is throwing me a little.

The below is a query that does what I want for retrieving data from two of the three physical files on the AS400.

select substr(clntpm,1,2) as clntwf, substr(pal#pm,1,10) as pal#wf, 
       substr(clsspm,1,2) as clsswf, clsqpm * 1 as clsqwf
from warpall
where locnpm <> 'ASSEMBLED PALLET'
  and commpm <> 'ASSEMBLED PALLET'
  and clsqpm <> 0

union all

select substr(clntpq,1,2) as clntwf, substr(pal#pq,1,10) as pal#wf, 
       substr(clsspq,1,2) as clsswf, clsqpq * 1 as clsqwf
from warpalq
where clsqpq <> 0

Now I want to add a third table... As I understand, I want to do an inner join on the result of the above union.

select * from (
<old query>
) t9
inner join t3 on <field> where t3.field = t9.field

Is what I tried, but I'm clearly getting the syntax wrong as I get an error about the where clause being unexpected.

Can anyone shed some light please?

Upvotes: 0

Views: 784

Answers (1)

Khan M
Khan M

Reputation: 415

try this 

select 
  * from (<old query>) as t9
inner join 
  t3 
on 
  t3.field = t9.field

Upvotes: 1

Related Questions