How to use where clause in the oracle join?

Hi Everyone,

I have the below query the inner join with the where clause.  Though, the query gets executed but does not return any value.  How to use where clause in the Oracle Inner Join?
Select U.a from U Inner Join E on
E.entity = U.entity Where U.Unique_name = 'AREA ID';

Open in new window

LVL 4
o0JoeCool0oAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
Try this:

Select U.a from U Inner Join E on
E.entity = U.entity and U.Unique_name = 'AREA ID';
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
technically, your query syntax is correct;
if it does not return any rows, it's a matter of DATA...

you might want a LEFT JOIN instead of a INNER JOIN to return the data from U even if there is no matching row in table E:

Select U.a from U LEFT Join E on
E.entity = U.entity Where U.Unique_name = 'AREA ID';

but if all you are interested in is the data from table U, you don't need the join to the table E anyhow..

so, we need to look at the larger picture here...

note: this might be a plain data type issue: if the field UNIQUE_NAME is CHAR , you might need to change it to VARCHAR2, and trim away any spaces from the data in the column  ...
0
o0JoeCool0oAuthor Commented:
@slightwv, I tried that but it does not give back any data.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

o0JoeCool0oAuthor Commented:
@Guy Hengel,  Thanks for explaining me in detail.  But I need to have the e table in the query and I tried the left outer join then it does not use any filter and gives me back 1000 rows.
0
magarityCommented:
You know that Oracle is case sensitive by default?  If E.entity contains "EntityA" and U.entity contains "entitya" then they won't join up.
PS - Could you please reveal how many rows are in E and how many are in U and maybe a sample of 3 or 4 rows from each that you thought were supposed to join up in your query but are not?  It's super hard to guess what is wrong with just a query and no examples.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
o0JoeCool0oAuthor Commented:
@magarity, The result should '1234',
2345,
3456,
4567. Numbers like this...
0
slightwv (䄆 Netminder) Commented:
What we would be looking for is raw data from the two tables and expected results so we can set up a test case and provide working code.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.