-1

I have 2 tables employeeAccount and employeeInfo. I am not allowed to modify the tables. Using Sybase databse.

employeeAccount 
=============================
employeeID   parentID
   S1           P1
   S2           P1
   S3           P1
   S4           P1
   S5           P1
   S6           P3
   S6           P4
   S7           P4
   S8           P4

 Second table:  employeeInfo 
 ==============================
 employeeID   Group     Status
   S1          0         
   S2          0        OPEN 
   S3          0        
   S4          1        
   S5          1
   S6          0        
   S7          0  
   S8          1

I want to select an employee whose status is open and all its siblings which have same group.

Example- S2 status is open and S2 siblings are S1,S3,S4,S5. I want the data for S1, S2 and S3 only, because all are from same group No 0 and have same ParentID P1 .

In my approach first I find the employee for OPEN status and then find its all the siblings. Then I select the only siblings which have same group.

select DISTINCT emp.employeeID, emp.parentID from employeeAccount emp
JOIN (
SELECT DISTINCT e.employeeID, e.parentID, info.Group FROM employeeAccount e, employeeInfo info
    WHERE e.employeeID = info.employeeID
    AND info.status = 'OPEN' 
) Details
ON Details.parentID = emp.parentID
WHERE Details.Group = (SELECT Group from employeeInfo where employeeID = emp.employeeID)

With this query I fetched S2 and his parent P1 because S2 status is OPEN then I fetched all the employees under parent P1 and then I select S1, S2 and S3 because they are from same group 0.

Please let me know if my approach is wrong or if any better approach is there.

user1841046
  • 93
  • 10

1 Answers1

0

You can simply try:-

SELECT E.employeeID, P.parentID
FROM EmpInfomartion E, EmpAccount P
WHERE Group IN (SELECT Group
                FROM EmpInfomartion
                WHERE Status = 'Open')
AND E.employeeID = P.employeeID;
Ankit Bajpai
  • 13,128
  • 4
  • 25
  • 40
  • How it will give S1, S2 and S3 – user1841046 Jul 20 '15 at 11:34
  • It will give S!, S2, S3 and S6, S7 also. Because they also belong to group 0, which is same as of S1, S2, and S3. – Ankit Bajpai Jul 20 '15 at 11:39
  • I think I was not able to explain the question well. It was my mistake. It should not include S6 and S7 if their status is not OPEN because they are from different parent. But as per your solution it will include S6 and S7 if their status is not OPEN. – user1841046 Jul 20 '15 at 13:13
  • It is same as SELECT P.employeeID, P.parentID FROM EmpInfomartion E, EmpAccount P WHERE E.groupID = 1 AND E.employeeID = P.employeeID ; – user1841046 Jul 20 '15 at 13:41
  • So here how will you determine which Ss are to pick. Means is ther some criteria to decide the particular ParentID – Ankit Bajpai Jul 20 '15 at 14:43
  • I need to select a group of Ss for which any of Ss status is OPEN and that comes under same ParentID. – user1841046 Jul 20 '15 at 16:09