How to select rows from tables
-
Consider the following tables
MANAGERID
101
102
103
104
MANAGERID-------EMPID
101 ----------------24
101-----------------25
101 ----------------26
104 --------------- 27
Write sql query to get the following output without using minus,union,intersect.
MANAGERID -------------------------EMPID
101 ---------------------------------- 24
101------------------------------------25
101 -----------------------------------26
102 -----------------------------------N/A
103 -----------------------------------N/A
104 -----------------------------------27
-
Is this a homework question?
John
-
, use left outer join and isNULL with 'N/A' -
Hey this is not working out
-
So please show us what you've tried so far and we'll see how we can help. We're not going to give you the answer on a plate, though.
John
-
DECLARE @managerIDs TABLE ( managerID INT )
DECLARE @managerEmps TABLE
( managerID INT
, empID INT
)
INSERT INTO @managerIDs
VALUES ( 101 )
INSERT INTO @managerIDs
VALUES ( 102 )
INSERT INTO @managerIDs
VALUES ( 103 )
INSERT INTO @managerIDs
VALUES ( 104 )
INSERT INTO @managerEmps
VALUES ( 101, 24 )
INSERT INTO @managerEmps
VALUES ( 101, 25 )
INSERT INTO @managerEmps
VALUES ( 101, 26 )
INSERT INTO @managerEmps
VALUES ( 104, 27 )
SELECT m.managerID
, COALESCE(CAST(empID AS CHAR(3)), 'N/A') AS empid
FROM @managerIDs AS m
LEFT JOIN @managerEmps AS me ON m.managerID = me.managerID
Note that I agree with John that we should not be doing the homework of others
Andras
Andras Belokosztolszki, MCPD, PhD
GoldenGate Software
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply