I have a select statement that works fine:
SELECT DISTINCT E.EmpNo, E.FirstName, E.Surname, A.StartDate, A.EndDate, Round((A.DurDays), 2) AS Days, Round((A.DurMins/60), 2) AS Hours, (K.Name) AS LeaveType,
decode (A.cost, '!!!!', 0, '!!#*', 1, '!!$1', 2, '!!%8', 3, '!!&?', 4, '!!(F', 5, '!!)M', 6,
'!!*T', 7, '!!+[', 8, '!!,b', 9, '!!-i', 10, '!!.p', 11, '!!/w', 12,
'!!0~', 13, '!!2(', 14, 25) as CostDays
from
clockwise.Employee E, clockwise.Absence A, clockwise.AbsKeys K,
ADMIN.BASIC_DETAILS@cwselinkptec c, ADMIN.EMP_POST_DETAILS@cwselinkptec d, ADMIN.POST_DETAILS@cwselinkptec f
WHERE E.EmpID = A.EmpID AND Trim(E.EmpNo) = Trim(c.EMPLOYEE_NUMBER)
AND A.StartDate >= to_date('01-12-2004','dd-mm-yyyy') AND A.StartDate <= to_date('01-01-2005','dd-mm-yyyy')
AND A.AbsKeyID = K.AbsKeyID AND (K.AbsKeyID = '0' OR K.AbsKeyID = '1') AND c.EMPLOYEE_NUMBER = d.EMPLOYEE_NUMBER
AND d.LINK_EFF_LINK = f.LINK_EFF_LINK AND f.post_location = '4106'
ORDER BY E.Surname ASC, A.StartDate

I get 26 rows returned. I need to add an extra field to the select. The Reason field on the Absence table contains a code, the value for this code, which I need, is stored in the Choices table. The Choices table has KEYID and ITEMID as the Primary Key. Reason field links to the ITEMID field.


When I run the following sql:
SELECT DISTINCT E.EmpNo, E.FirstName, E.Surname, A.StartDate, A.EndDate, Round((A.DurDays), 2) AS Days, Round((A.DurMins/60), 2) AS Hours, (K.Name) AS LeaveType,
decode (A.cost, '!!!!', 0, '!!#*', 1, '!!$1', 2, '!!%8', 3, '!!&?', 4, '!!(F', 5, '!!)M', 6,
'!!*T', 7, '!!+[', 8, '!!,b', 9, '!!-i', 10, '!!.p', 11, '!!/w', 12,
'!!0~', 13, '!!2(', 14, 25) as CostDays, (B.Name) as SLReason
FROM clockwise.Employee E, clockwise.Absence A, clockwise.AbsKeys K, clockwise.choices B,
ADMIN.BASIC_DETAILS@cwselinkptec c, ADMIN.EMP_POST_DETAILS@cwselinkptec d, ADMIN.POST_DETAILS@cwselinkptec f
WHERE E.EmpID = A.EmpID AND Trim(E.EmpNo) = Trim(c.EMPLOYEE_NUMBER)
AND A.StartDate >= to_date('01-12-2004','dd-mm-yyyy') AND A.StartDate <= to_date('01-01-2005','dd-mm-yyyy')
AND A.AbsKeyID = K.AbsKeyID AND (K.AbsKeyID = '0' OR K.AbsKeyID = '1') AND c.EMPLOYEE_NUMBER = d.EMPLOYEE_NUMBER
AND d.LINK_EFF_LINK = f.LINK_EFF_LINK AND f.post_location = '4106'
AND B.KeyID = 'R%' AND B.ItemID = A.Reason
ORDER BY E.Surname ASC, A.StartDate

I get 1 row, the only person from the previous select who has an entry in the Choices table. I understand why.

When I run the following sql:
SELECT DISTINCT E.EmpNo, E.FirstName, E.Surname, A.StartDate, A.EndDate, Round((A.DurDays), 2) AS Days, Round((A.DurMins/60), 2) AS Hours, (K.Name) AS LeaveType,
decode (A.cost, '!!!!', 0, '!!#*', 1, '!!$1', 2, '!!%8', 3, '!!&?', 4, '!!(F', 5, '!!)M', 6,
'!!*T', 7, '!!+[', 8, '!!,b', 9, '!!-i', 10, '!!.p', 11, '!!/w', 12,
'!!0~', 13, '!!2(', 14, 25) as CostDays, SLReason
FROM (SELECT (B.Name) SLReason
FROM clockwise.choices B,
clockwise.Absence A
WHERE B.KeyID = 'R%'
AND B.ItemID = A.Reason),
clockwise.Employee E, clockwise.Absence A, clockwise.AbsKeys K,
ADMIN.BASIC_DETAILS@cwselinkptec c, ADMIN.EMP_POST_DETAILS@cwselinkptec d, ADMIN.POST_DETAILS@cwselinkptec f
WHERE E.EmpID = A.EmpID AND Trim(E.EmpNo) = Trim(c.EMPLOYEE_NUMBER)
AND A.StartDate >= to_date('01-12-2004','dd-mm-yyyy') AND A.StartDate <= to_date('01-01-2005','dd-mm-yyyy')
AND A.AbsKeyID = K.AbsKeyID AND (K.AbsKeyID = '0' OR K.AbsKeyID = '1') AND c.EMPLOYEE_NUMBER = d.EMPLOYEE_NUMBER
AND d.LINK_EFF_LINK = f.LINK_EFF_LINK AND f.post_location = '4106'
ORDER BY E.Surname ASC, A.StartDate

I get 104 rows, each person from the first select now appears 4 times with 1 of 4 different reasons.

How do I get 26 rows with just the 1 person showing his reason and the 25 other's having blank reasons.

Thanks for any help.

Have a look at the Join predicate. Rather than define you realtionship using a where clause use a join. The join can cater for the situation where the value is null.

Sorry, don't have time to examine id detail your SQL but will try to later on tonight, that is if no one else has come up with a better answer in the mean time.

What you need is a LEFT JOIN.

Based on the SQL that you've posted, you appear to be using Oracle, but I can't tell which version. If your Oracle is current enough to support the SQL-92 syntax, I'd strongly suggest that you switch to it because it makes a lot of things easier than the SQL-89 syntax that you are using in this query.

If you need more help, please post your Oracle version and whatever question(s) you might have.

-PatP

I'm using Oracle8i, since I posted I've been trying to use a join (left outer) with no success so far.

Thanks for the info, I'll keep trying.

Got it working by totally ignoring the choices table. I was getting more comlicated than I needed, I just added a decode for the Reason field:
SELECT DISTINCT E.EmpNo, E.FirstName, E.Surname, A.StartDate, A.EndDate, Round((A.DurDays), 2) AS Days, Round((A.DurMins/60), 2) AS Hours, (K.Name) AS LeaveType,
decode(A.Cost, '!!!!', 0, '!!#*', 1, '!!$1', 2, '!!%8', 3, '!!&?', 4, '!!(F', 5, '!!)M', 6,
'!!*T', 7, '!!+[', 8, '!!,b', 9, '!!-i', 10, '!!.p', 11, '!!/w', 12,
'!!0~', 13, '!!2(', 14, 25) as CostDays,
Decode(A.Reason, '!', 'Certified', '#', 'Hospitalised', '$', 'Uncertified', '%', 'Unwell',
'&', 'Throat Infection', Null) as SLReason
FROM clockwise.Employee E, clockwise.Absence A, clockwise.AbsKeys K,
ADMIN.BASIC_DETAILS@cwselinkptec c, ADMIN.EMP_POST_DETAILS@cwselinkptec d, ADMIN.POST_DETAILS@cwselinkptec f
WHERE E.EmpID = A.EmpID AND Trim(E.EmpNo) = Trim(c.EMPLOYEE_NUMBER)
AND A.StartDate >= to_date('01-12-2004','dd-mm-yyyy') AND A.StartDate <= to_date('01-01-2005','dd-mm-yyyy')
AND A.AbsKeyID = K.AbsKeyID AND (K.AbsKeyID = '0' OR K.AbsKeyID = '1') AND c.EMPLOYEE_NUMBER = d.EMPLOYEE_NUMBER
AND d.LINK_EFF_LINK = f.LINK_EFF_LINK AND f.post_location = '4106'
ORDER BY E.Surname ASC, A.StartDate

I got the 26 rows with the correct value.

Thanks for the help,
Mark.