• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Ron McLeod
  • Paul Clapham
  • Bear Bibeault
  • Junilu Lacar
Sheriffs:
  • Jeanne Boyarsky
  • Tim Cooke
  • Henry Wong
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • salvin francis
  • Frits Walraven
Bartenders:
  • Scott Selikoff
  • Piet Souris
  • Carey Brown

Oracle Database SQL confusion

 
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!
 
Marshal
Posts: 25798
69
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.
 
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.
 
Do you pee on your compost? Does this tiny ad?
Thread Boost feature
https://coderanch.com/t/674455/Thread-Boost-feature
    Bookmark Topic Watch Topic
  • New Topic