BookmarkSubscribeRSS Feed
tmm
Fluorite | Level 6 tmm
Fluorite | Level 6

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.

3 REPLIES 3
Reeza
Super User

Yes.

tmm
Fluorite | Level 6 tmm
Fluorite | Level 6

So how exactly would i go about doing that?

Reeza
Super User

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.

sas-innovate-2024.png

Today is the last day to save with the early bird rate! Register today for just $695 - $100 off the standard rate.

 

Plus, pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 705 views
  • 0 likes
  • 2 in conversation