inner and left

[tmm]

Can you do an inner join and a left join within a single extraction? Meaning if I am connecting to our server to the database via a DB2 connection, can I create a query with a subquery where the outer query is the inner join and the subquery is a left join?

The reason is I have to pull from a series of tables as my inner with some filters that gives me 55480 rows. Yet when I add the final table to connect to pull pricing information such as calculated amounts (these fields are only in one table) it returns 586 rows. That is because the main query is saying I only want these procedure codes but I want the entire claim associated. So the claim might have dozen different procedure codes.

The table I am pulling the amounts from do not have many of these type procedure codes so that is why doing an inner to it I get 586. I have to do a left I believe so it will keep my 55480 rows but give me all the monetary information that is associated with the claim itself and I would guess the fst srvc date. Not sure really.

[Reeza]

Yes.

[tmm]

So how exactly would i go about doing that?

[Reeza]

Write your subquery and test it, then add the outer query.

You don't have a ton of data so you might be better off separating your queries into smaller sections so you can see what's going on instead of writing nested ones.

As always you should post some data in/out, what you have and what you expect. Also your code could be helpful.