Joins

[sasenthusiast07]

Good evening all,

I have an question in inner join ,

I have performed inner join like this 

proc sql;

create table combine as

select distinct

n.apple,

p.vegges

from 

fruits as n

inner join 

vegetables as p

on 

n.key=p.key;

quit;

Now in vegges column I have carrots,beans,brocoli,beets--I like my data only to have carrot and beans , how will I apply this condition here.

Thank you

S 

[jimbarbour]

You probably just need a WHERE clause just before the semi-colon.

WHERE vegge IN (carrot, beans)

However, remember that if you're doing an Inner join, only items found in both tables will be present in the results set.  I'm not sure that carrot or beans will be found in an Inner join with fruits.

Jim

[sasenthusiast07]

Thanks for the reply.

Appreciate it.

How will I address this issue if not inner join

I have 2 tables A and B

A has Vehicle,Bus,Train

B has Vehicle,road,car

In B table I need only Toyota , camry from car variable 

I can join these tables by vehicle as an common variable 

How should I approach this condition?

[Reeza · Accepted solution]

The question is, what do you want to happen if that value is not in both tables? What do you want as the output? INNER join only works if it's both tables, if it's one and not the other then you would need to use a LEFT/RIGHT JOIN. Personally, I almost never use an INNER join, I use right/left instead that way if missing values are present they would have missing values and it's an obvious error that's easier to notice. If you use an inner join and accidentally drop a category that's much harder to know.