Please help with SQL join

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

al_galiza - 31 Jan 2006 16:05 GMT

I have a query that joins multiple tables. The key across all the tables is
the SSN column, which I select as Distinct. When I left join another table
(tblLanguages) where the SSN is found multiple times, the resultset then
multiplies depending on the number of times the SSN is found in tblLanguages.
I have to do a left join to obtain and keep all the records from the original
table. How do I join the tblLanguages table without the results multiplying?

Thank you for any help.

<cfquery datasource="#application.App_datasource#" name="get_emps"
blockfactor="100">
    select distinct M.SSN, M.LastName, M.FirstName, E.School, E.description,
M.Gender, M.photo, M.resume, P.Non_Mgrl_Lvl, P.Mgrl_Lvl, M.WorkFor, D.DeptName,
P.CSTitleDesc, P.OfficeTitle, P.LeaveStatus, P.DistrCode, P.DeptCode,
E.Degree_ID, E.GradYear, E.Degree_Earned, DM.DegreeName, V.DivisionName,
L.ForeignLanguage, M.EthnicCode, Eth.EthnicDesc
        from tblEmployeeMain M left join
            tblEmployeePayroll P on M.SSN=P.SSN left join
            tblEmployeeType T on T.SSN=M.SSN left join
            tblAgencyDptMst D on P.DistrCode=D.DeptCode left join
            tblAgencyDivisionMst V on D.DivisionID = V.DivisionID left join
            tblEducation E on M.SSN = E.SSN inner join
            tblEducationDegreeMst DM on E.Degree_ID = DM.DegreeID left join
            tblLanguage L on M.SSN = L.SSN left join
            tblEthnicCodeMst ETH on M.EthnicCode = ETH.EthnicCode left join
            tblTraining TR on M.SSN = TR.SSN</cfquery>

Reply to this Message

al_galiza - 31 Jan 2006 16:59 GMT

Just tried it. Still having the same problem. However, I noticed that if I
remove L.ForeignLanguage from select, I get the correct results. I need to
pull the L.ForeignLanguage column without the record replication.

Reply to this Message

al_galiza - 31 Jan 2006 20:36 GMT

Originally posted by: CF_Oracle
You probably have multiple languages for some presons, so results with
multiple SSNs are right. Where do you need then result from this query? Maybe
you can split the original query in 2 moving ForeignLanguage column in other
query and use results of the second query for drill down or list per SSN?

Not sure what you mean by multiple languages. If you mean multiple language
columns from different tables, that's a negative. There are different
languages the tblLanguage table though. The issue here is that if a user
speaks 2 languages, for example, the SSN will be found 2x in tblLanguage, which
then doubles the original recordset. I've never dealt with this before, so any
help would be appreciated in getting this query right. If not, I'll probably
resort to breaking up the query. Here's my latest code:

select distinct M.SSN, M.FirstName, M.LastName, P.Non_Mgrl_Lvl, P.Mgrl_Lvl,
M.WorkFor, P.CSTitleDesc, P.OfficeTitle, P.LeaveStatus, P.DistrCode,
P.DeptCode, D.DeptName, E.Degree_ID, E.GradYear, E.Degree_Earned, E.School,
E.Description, DM.DegreeName, M.EthnicCode, Eth.EthnicDesc, TR.Course,
L.ForeignLanguage
        from
            (select distinct LastName, FirstName, SSN, EthnicCode, WorkFor from
tblEmployeeMain) M inner join
            (select SSN, Non_Mgrl_Lvl, Mgrl_Lvl, CSTitleDesc, OfficeTitle, LeaveStatus,
DistrCode, DeptCode from tblEmployeePayroll where DistrCode is not null) P on
M.SSN=P.SSN left join
            (select DeptName, DeptCode, DivisionID from tblAgencyDptMst) D on
P.DistrCode=D.DeptCode inner join
            (select DivisionName, DivisionID from tblAgencyDivisionMst) V on
D.DivisionID = V.DivisionID inner join
            (select distinct school, ssn, degree_id, gradyear, description,
degree_earned from tblEducation) E on M.SSN = E.SSN left join
            (select degreeName, degreeID from tblEducationDegreeMst) DM on E.Degree_ID
= DM.DegreeID left join
            tblEthnicCodeMst ETH on M.EthnicCode = ETH.EthnicCode left join
            (select distinct SSN, Course, CourseLevel, DateCompleted from tblTraining)
TR on M.SSN = TR.SSN left join
            (select distinct SSN, foreignlanguage from tbllanguage) L on M.SSN = L.SSN

Reply to this Message

Webmaster Forum / ColdFusion / Advanced Techniques / January 2006

Please help with SQL join