Win a copy of The Java Performance Companion this week in the Performance forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Oracle Database SQL confusion

 
bob reilly
Ranch Hand
Posts: 44
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Sheriff
Posts: 21136
32
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 118
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Could be duplicate wbt_id in table 1 or table 2?

Agad
 
bob reilly
Ranch Hand
Posts: 44
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic