aspose file tools*
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


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "Oracle Database SQL confusion" Watch "Oracle Database SQL confusion" New topic
Author

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,
tbl3.emp_fullname,
tbl4.emp_fullname,
tbl4.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
Bartender

Joined: Oct 14, 2005
Posts: 18651
    
    8

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?

Agad
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: http://aspose.com/file-tools
 
subject: Oracle Database SQL confusion