May 12, 2010 at 1:08 am
SELECT EP.EMPLOYEE_ID, EP.FIRST_NAME + ' ' + ISNULL(EP.MIDDLE_NAME, ' ') + ' ' + ISNULL(EP.LAST_NAME, ' ') AS FULL_NAME, MB.BUSINESSUNIT_NAME,
CONVERT(VARCHAR(10), EP.DATE_OF_BIRTH, 101) AS DATE_OF_BIRTH, EP.MOBILE, CONVERT(VARCHAR(10), ECM.RECRUITMENT_DATE, 101) AS JOINING_DATE,
EC.PRESENT_ADDRESS_LINE_1, EC.PRESENT_ADDRESS_LINE_2, EC.PRESENT_ADDRESS_LINE_3
FROM EIS_PERSONAL EP LEFT JOIN EIS_CONTACTS EC ON EP.EMPLOYEE_ID = EC.EMPLOYEE_ID
LEFT JOIN EIS_COMPANY ECM ON EP.EMPLOYEE_ID = ECM.EMPLOYEE_ID
LEFT JOIN MASTER_BUSINESSUNIT MB ON ECM.BUSINESSUNIT_ID = MB.BUSINESSUNIT_ID
RIGHT JOIN EMPLOYEE_RESIGNATION ER ON ER.TRANSFER_EMP_ID = EP.EMPLOYEE_ID
RIGHT JOIN EMPLOYEE_TERMINATION ET ON ET.TRANSFER_EMP_ID = EP.EMPLOYEE_ID
WHERE ECM.BUSINESSUNIT_ID != 12
ORDER BY ECM.BUSINESSUNIT_ID
In the above query, there are some employee ID's in EMPLOYEE_RESIGNATION and EMPLOYEE_TERMINATION table and i m match it with other table as shown above.
I want to eliminate details that match ID's with the above mentioned 2 table.
How do i do this.
Thank u all
May 12, 2010 at 1:43 am
Do you want to eliminate the IDs in the result set that are in EMPLOYEE_RESIGNATION or EMPLOYEE_TERMINATION? is that what you want?
May 12, 2010 at 1:45 am
yeah atif that is want i wanted to do. There are almost 100 rows and i m deleting manually.
If u can help me it will be a great help.
May 12, 2010 at 4:36 am
Hey there, you can use on of the following
SELECT
EP.EMPLOYEE_ID,
EP.FIRST_NAME + ' ' + ISNULL(EP.MIDDLE_NAME, ' ') + ' ' + ISNULL(EP.LAST_NAME, ' ') AS FULL_NAME,
MB.BUSINESSUNIT_NAME,
CONVERT(VARCHAR(10), EP.DATE_OF_BIRTH, 101) AS DATE_OF_BIRTH,
EP.MOBILE, CONVERT(VARCHAR(10), ECM.RECRUITMENT_DATE, 101) AS JOINING_DATE,
EC.PRESENT_ADDRESS_LINE_1,
EC.PRESENT_ADDRESS_LINE_2,
EC.PRESENT_ADDRESS_LINE_3
FROM
EIS_PERSONAL EP
LEFT JOIN EIS_CONTACTS EC ON EP.EMPLOYEE_ID = EC.EMPLOYEE_ID
LEFT JOIN EIS_COMPANY ECM ON EP.EMPLOYEE_ID = ECM.EMPLOYEE_ID
LEFT JOIN MASTER_BUSINESSUNIT MB ON ECM.BUSINESSUNIT_ID = MB.BUSINESSUNIT_ID
--=== NEW ADDITION
LEFT JOIN / RIGHT / INNER -- Remove whichever join u dont need
( SELECT DISTINCT TRANSFER_EMP_ID EMP_ID FROM EMPLOYEE_RESIGNATION
UNION
SELECT DISTINCT TRANSFER_EMP_ID EMP_ID FROM EMPLOYEE_TERMINATION
) A
ON A.EMP_ID = EP.EMPLOYEE_ID
WHERE
ECM.BUSINESSUNIT_ID != 12
ORDER BY
ECM.BUSINESSUNIT_ID
or
SELECT
EP.EMPLOYEE_ID,
EP.FIRST_NAME + ' ' + ISNULL(EP.MIDDLE_NAME, ' ') + ' ' + ISNULL(EP.LAST_NAME, ' ') AS FULL_NAME,
MB.BUSINESSUNIT_NAME,
CONVERT(VARCHAR(10), EP.DATE_OF_BIRTH, 101) AS DATE_OF_BIRTH,
EP.MOBILE, CONVERT(VARCHAR(10), ECM.RECRUITMENT_DATE, 101) AS JOINING_DATE,
EC.PRESENT_ADDRESS_LINE_1,
EC.PRESENT_ADDRESS_LINE_2,
EC.PRESENT_ADDRESS_LINE_3
FROM
EIS_PERSONAL EP
LEFT JOIN EIS_CONTACTS EC ON EP.EMPLOYEE_ID = EC.EMPLOYEE_ID
LEFT JOIN EIS_COMPANY ECM ON EP.EMPLOYEE_ID = ECM.EMPLOYEE_ID
LEFT JOIN MASTER_BUSINESSUNIT MB ON ECM.BUSINESSUNIT_ID = MB.BUSINESSUNIT_ID
--=== NEW ADDITION
WHERE
ECM.BUSINESSUNIT_ID != 12
AND EP.EMPLOYEE_ID IN
(
SELECT DISTINCT TRANSFER_EMP_ID EMP_ID FROM EMPLOYEE_RESIGNATION
UNION
SELECT DISTINCT TRANSFER_EMP_ID EMP_ID FROM EMPLOYEE_TERMINATION
)
ORDER BY
ECM.BUSINESSUNIT_ID
Hope this helps you!
Cheers!
May 12, 2010 at 4:55 am
sachinrshetty (5/12/2010)
FROM EIS_PERSONAL EP LEFT JOIN EIS_CONTACTS EC ON EP.EMPLOYEE_ID = EC.EMPLOYEE_IDLEFT JOIN EIS_COMPANY ECM ON EP.EMPLOYEE_ID = ECM.EMPLOYEE_ID
LEFT JOIN MASTER_BUSINESSUNIT MB ON ECM.BUSINESSUNIT_ID = MB.BUSINESSUNIT_ID
RIGHT JOIN EMPLOYEE_RESIGNATION ER ON ER.TRANSFER_EMP_ID = EP.EMPLOYEE_ID
RIGHT JOIN EMPLOYEE_TERMINATION ET ON ET.TRANSFER_EMP_ID = EP.EMPLOYEE_ID
In the above query, there are some employee ID's in EMPLOYEE_RESIGNATION and EMPLOYEE_TERMINATION table and i m match it with other table as shown above.
If you eliminate rows that match one of those two tables, I think you'll eliminate everything as you've RIGHT joined to them, meaning (correct me if I'm wrong) that you will ONLY be showing rows currently that appear in one or both of these tables?
I think you need to use a LEFT join with an 'IS NULL' condition in the where clause, AKA an 'anti' join.
My example :
/* SETUP */
declare @emp table (empId int, empName varchar(15))
declare @empTerm table (empId int, empTermDate datetime)
declare @empResig table (empId int, empResigDate datetime)
insert into @emp (empId, empName) values (1, 'Kev')
insert into @emp (empId, empName) values (2, 'Jeff Quit')
insert into @emp (empId, empName) values (3, 'Bob')
insert into @emp (empId, empName) values (4, 'Dave Sacked')
insert into @emp (empId, empName) values (5, 'Ian')
insert into @emp (empId, empName) values (6, 'Harry Redundant')
insert into @empResig(empId, empResigDate) values (2, GETDATE()-30)
insert into @empTerm(empId, empTermDate) values (4, GETDATE()-60)
insert into @empTerm(empId, empTermDate) values (6, GETDATE()-90)
/* QUERIES */
select 'Term Only', * from @emp e RIGHT JOIN @empTerm et ON e.empId = et.empId
select 'Resig Only', * from @emp e RIGHT JOIN @empResig er ON e.empId = er.empId
select 'Your Query', * from @emp e RIGHT JOIN @empTerm et ON e.empId = et.empId RIGHT JOIN @empResig er ON e.empId = er.empId
select 'My Query', * from @emp e
LEFT JOIN @empTerm et ON e.empId = et.empId
LEFT JOIN @empResig er ON e.empId = er.empId
WHERE et.empId IS NULL AND er.empId IS NULL
-------------------------------Oh no!
May 12, 2010 at 5:11 am
Thank u Kevin and coldcoffee for your help. I will try your logic and revert back to u.
Thanks a lot.
Sachin
May 12, 2010 at 5:15 am
It should be added at this point that ColdCoffee's solutions produce the set you wish to exclude (i.e. those that ARE in one of those tables) whereas mine is intended to adjust your original query such that it will return all rows which are NOT in one of those tables and then you would skip the deletion stage.
Take your pick of what you were trying to achieve.
-- Kev
-------------------------------Oh no!
May 12, 2010 at 5:53 am
May be of interest here - http://sqlinthewild.co.za/index.php/2010/03/23/left-outer-join-vs-not-exists/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply