Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
joesmithf1
Starting Member
6 Posts |
 Posted - 2011-01-05 : 01:49:29
|
| Hi All, I am fairly new to SQL, so I know the basic stuff. But now I am trying something more advance. ? Please help. Below is my SELECT statement, but what I really want to do is the following: 1.EMPLOYEE_PAYS is my main table, so I want to get the employee’s name, address, etc. from EMPLOYEE_INFO2.Then I want to compare it(Employee_Pays) against PAY_DETL to see if anyone in EMPLOYEE_PAYS matches with anyone in PAY_DETL. If there is no match, then report the unmatch in the EMPLOYEE_PAYS.Thank you. JoeSELECT DP.INTERNAL_EMPL_ID,DP.DEDTYP_CD,DP.DEDPLAN_CD,DP.EFFECTIVE_DT,DP.OVRD_DED_AM,DP.OVRD_DED_PC, SIMP.NAME, SIMP.ADDRESS,DP.EXPIRATION_DT FROM EMPLOYEE_PAYS DP LEFT JOIN EMPLOYEE_INFO SIMP ON DP.INTERNAL_EMPL_ID=SIMP.INTERNAL_EMPL_ID LEFT JOIN PAY_DETL PD ON DP.INTERNAL_EMPL_ID=PD.INTERNAL_EMPL_ID WHERE DP.EXPIRATION_DT= TO_DATE('12/31/9999','MM/DD/YYYY') |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-01-05 : 01:52:33
|
| SELECT DP.INTERNAL_EMPL_ID,DP.DEDTYP_CD,DP.DEDPLAN_CD,DP.EFFECTIVE_DT,DP.OVRD_DED_AM,DP.OVRD_DED_PC, SIMP.NAME, SIMP.ADDRESS,DP.EXPIRATION_DT, case when PD.INTERNAL_EMPL_ID is null then 'unmatch' else 'match' endFROM EMPLOYEE_PAYS DPLEFT JOIN EMPLOYEE_INFO SIMP ON DP.INTERNAL_EMPL_ID=SIMP.INTERNAL_EMPL_ID LEFT JOIN PAY_DETL PDON DP.INTERNAL_EMPL_ID=PD.INTERNAL_EMPL_ID WHERE DP.EXPIRATION_DT= TO_DATE('12/31/9999','MM/DD/YYYY') ==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
joesmithf1
Starting Member
6 Posts |
Posted - 2011-01-05 : 02:22:01
|
| wow! that was a lightning fast respond! thank you! now if i may, what if i add two "AND" statementS to the end of this Select statement? when i ran the statement, the query returned multiple duplicate rows for each employee, because i am combining tables. I tried the "DISTINCT" function on INTERNAL_EMPL_ID, but it gives me only one row per Employee ID; i want one row per each change on DEDTYP_CD instead. For example000005898 - 01FBE000005898 - 01FNAAND SUBSTR(DEDTYP_CD,1,2)='01' AND SUBSTR(DEDPLAN_CD,2,2)='15' |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-01-05 : 02:30:40
|
| Do you mean one row for each distinct DEDTYP_CD?It's on EMPLOYEE_PAYS so you should be getting that anyway.maybeSELECT DP.INTERNAL_EMPL_ID,DP.DEDTYP_CD,DP.DEDPLAN_CD,DP.EFFECTIVE_DT,DP.OVRD_DED_AM,DP.OVRD_DED_PC, SIMP.NAME, SIMP.ADDRESS,DP.EXPIRATION_DT, case when PD.INTERNAL_EMPL_ID is null then 'unmatch' else 'match' endFROM EMPLOYEE_PAYS DPLEFT JOIN EMPLOYEE_INFO SIMP ON DP.INTERNAL_EMPL_ID=SIMP.INTERNAL_EMPL_ID LEFT JOIN (select distinct INTERNAL_EMPL_ID from PAY_DETL PD) PAY_DETL PDON DP.INTERNAL_EMPL_ID=PD.INTERNAL_EMPL_ID WHERE DP.EXPIRATION_DT= TO_DATE('12/31/9999','MM/DD/YYYY') AND SUBSTR(DP.DEDTYP_CD,1,2)='01' AND SUBSTR(DP.DEDPLAN_CD,2,2)='15'==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
joesmithf1
Starting Member
6 Posts |
Posted - 2011-01-05 : 02:42:36
|
| the employee could get different types of pay. In this case, 01FBE and 01FNA. So when i run my select, i would like to see every type of pays that an employee has in the Employee_Pays table. Thanks again. Let me play around with your statement when i get back to work tomorrow and see what happens. |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2011-01-05 : 03:39:59
|
| Also note that this site is for MS SQL Server. Ask Oracle related questions at oracle forums such as www.orafaq.comMadhivananFailing to plan is Planning to fail |
|
|
|
|
|
|
|
|
Outer Join