Home » SQL & PL/SQL » SQL & PL/SQL » Unable to retrieve all records (Oracle, SQL)
Unable to retrieve all records [message #424481] Fri, 02 October 2009 10:46 Go to next message
nedandkay
Messages: 1
Registered: October 2009
Location: Canada
Junior Member
I am running a script to get employees and addresses. Some employees have an alternate address to the main address... I can tell those people by there emp_id in the address table... If the emp_id is null then that employee has no alternate address. The problem wiith my script below is that my results show only the employees with an alternate address but it does not show the rest of the employees who have no alternate address. Thanks for any help!

-- My script

select ht.unit_order, ht.unit_level,u.unit_name, e.EMP_FIRST_NAME, e.EMP_LAST_NAME,
a.BUILDING_CODE, a.ADDRESS_1, a.ADDRESS_2, a.ADDRESS_3

from hierarchy_temp ht, unit u, office_employee oe, employee e, address a

where ht.unit_id = u.unit_id
and u.unit_id = oe.unit_id
and oe.emp_id = e.emp_id
and u.unit_id = a.UNIT_ID
and e.emp_id = a.emp_id(+)
and u.unit_name = 'XXX BRANCH'



order by ht.unit_order asc,
ht.unit_level asc,
Re: Unable to retrieve all records [message #424483 is a reply to message #424481] Fri, 02 October 2009 11:09 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
If we had had DDL for tables & DML for test data,
then we could reproduce problem & possible solution.

Until then enjoy your mystery.

SELECT   ht.unit_order, 
         ht.unit_level, 
         u.unit_name, 
         e.emp_first_name, 
         e.emp_last_name, 
         a.building_code, 
         a.address_1, 
         a.address_2, 
         a.address_3 
FROM     hierarchy_temp ht, 
         unit u, 
         office_employee oe, 
         employee e, 
         address a 
WHERE    ht.unit_id = u.unit_id 
         AND u.unit_id = oe.unit_id 
         AND oe.emp_id = e.emp_id 
         AND u.unit_id = a.unit_id 
         AND e.emp_id = a.emp_id (+) 
         AND u.unit_name = 'XXX BRANCH' 
ORDER BY ht.unit_order ASC, 
         ht.unit_level ASC
Re: Unable to retrieve all records [message #424486 is a reply to message #424481] Fri, 02 October 2009 11:31 Go to previous message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
nedandkay wrote on Fri, 02 October 2009 17:46
If the emp_id is null then that employee has no alternate address. The problem wiith my script below is that my results show only the employees with an alternate address but it does not show the rest of the employees who have no alternate address. Thanks for any help!

-- My script

select <some columns>
from hierarchy_temp ht,
unit u,
office_employee oe,
employee e,
address a
where ht.unit_id = u.unit_id
and u.unit_id = oe.unit_id
and oe.emp_id = e.emp_id
and u.unit_id = a.UNIT_ID
and e.emp_id = a.emp_id(+)
and u.unit_name = 'XXX BRANCH'
order by ht.unit_order asc,
ht.unit_level asc

If EMP_ID is null, then the employees cannot be joined to OE table. Probably you need outer join to that branch (OE, U, HT) too - but all its column will be NULL.

P.S. Thanks BlackSwan for formatting the code - it was really easy to spot it.
Previous Topic: Records
Next Topic: count rows in tables
Goto Forum:
  


Current Time: Sat Dec 03 08:16:54 CST 2016

Total time taken to generate the page: 0.18158 seconds