JET SQL: Join two tables on different columns of e

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.

All Forums

Other Forums

MS Access

JET SQL: Join two tables on different columns of e

Author

Topic

dimples604
Starting Member

1 Post

Posted - 2012-09-27 : 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!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources