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):
from table1 tbl1,
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!
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.