?
Solved

How to use where clause in the oracle join?

Posted on 2014-07-16
7
Medium Priority
?
793 Views
Last Modified: 2014-07-16
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

0
Comment
Question by:o0JoeCool0o
7 Comments
 
LVL 78

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 668 total points
ID: 40199729
Try this:

Select U.a from U Inner Join E on
E.entity = U.entity and U.Unique_name = 'AREA ID';
0
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 664 total points
ID: 40199733
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
 
LVL 4

Author Comment

by:o0JoeCool0o
ID: 40199750
@slightwv, I tried that but it does not give back any data.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 4

Author Comment

by:o0JoeCool0o
ID: 40199759
@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
 
LVL 13

Accepted Solution

by:
magarity earned 668 total points
ID: 40199875
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
 
LVL 4

Author Comment

by:o0JoeCool0o
ID: 40200007
@magarity, The result should '1234',
2345,
3456,
4567. Numbers like this...
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40200016
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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question