File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes Oracle/OAS and the fly likes Oracle Database SQL confusion Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "Oracle Database SQL confusion" Watch "Oracle Database SQL confusion" New topic

Oracle Database SQL confusion

bob reilly
Ranch Hand

Joined: Jan 22, 2007
Posts: 44
I've been asked to modify an Oracle query. While I have fundamentatl SQL skills - my sophistication could use some improvement..

Here's a query (it's for example purposes):
select tbl3.emp_name,
from table1 tbl1,
table2 tbl2,
employee tbl3,
employee tbl4
where tbl2.org_name like '%HOME%'
and tbl1.org_home_team = 'Y'
and tbl3.emp_id = tbl1.emp_id
and tbl1.emp_id = tbl4.emp_id
and tbl2.wbt_id = tbl1.wbt_id
and sysdate between tbl4.EMP_EFFECTIVE_DATE and tbl4.EMP_TERMINATION_DATE;

My question - tbl3.emp_name and tbl4.emp_name both point to the same column in the same table; when when the query is run they yield different information. I was surprised to see the same table mentioned twice in the same query, then I saw the same column names.

How is that different information is retrieved. Any explanation here would be appreciated. Thanks!
Paul Clapham

Joined: Oct 14, 2005
Posts: 19973

If they yield different information then presumably the data is coming from different rows in the table. I don't see anything in that query which forces Row N of tbl3 to be joined to Row N of tbl4.
Agador Paloi
Ranch Hand

Joined: Jan 24, 2006
Posts: 118
Could be duplicate wbt_id in table 1 or table 2?

bob reilly
Ranch Hand

Joined: Jan 22, 2007
Posts: 44
After some additional analysis - it came down to the query structure. It fooled me. I thought perhaps there was more to it but it was the query. I never saw in one query multiple references to the same table unless of course there was a subquery. I parsed out the query with for lack of a better explanation a front end a back end query. Sure enough when I used the second reference with all appropriate joins - the 'different' data was retrieved. My experience base needed some expansion and I got it. I don't know if I would have written the same way but that was not for me to say.

Thank you both for responding.
I agree. Here's the link:
subject: Oracle Database SQL confusion
It's not a secret anymore!