|
dimples604
Starting Member
1 Posts |
 Posted - 09/27/2012 : 18:40:38
|
TABLE dbo_R5PERSONNEL
column PER_DESC
Do, John
Jones, Jacky
TABLE dbo_R5USERS
column USR_DESC
John Do
Jack Jones
Q: How would I join these two tables to get all the USR_DESC from Table dbo_R5USERS that exist in column PER_DESC in Table dbo_R5PERSONNEL.
I used string manipulation to capitalize and extract just the last names from each table
SELECT UCASE(MID(TRIM(dbo_R5USERS.USR_DESC), INSTR(TRIM(dbo_R5USERS.USR_DESC), ' ') + 1, LEN(TRIM(dbo_R5USERS.USR_DESC)) - INSTR(TRIM(dbo_R5USERS.USR_DESC), ' '))) AS LastName
FROM dbo_R5USERS
WHERE dbo_R5USERS.USR_ACTIVE = '+'
ORDER BY dbo_R5USERS.USR_DESC;
SELECT UCASE(MID(TRIM(dbo_R5PERSONNEL.PER_DESC),1,INSTR(TRIM(dbo_R5PERSONNEL.PER_DESC),',')-1)) AS LastName
FROM dbo_R5PERSONNEL
WHERE dbo_R5PERSONNEL.PER_NOTUSED='+'
ORDER BY dbo_R5PERSONNEL.PER_DESC;
How would I incorporate a JOIN on these two subqueries? Is there another method to achieve my desired results?
Thanks in advance! |
|
JET SQL: Join two tables on different columns of e
