COSMTARFCU
asked on
Outer Joins on multiple tables
I am trying to run a query using MSQuery off a Cache 5.2.3 database, and am having an issue creating an outer join becuase the query requires 3 separate tables. When I try to change one of the inner joins to an outer join it tells me that I can't have an outer join with three tables. How can I get around that? Here is the sql code it is running (I have changed table names, and field names for security purposes)...
SELECT ACCT1.acn1, ACCT1.mbr_1st_nam, Demographics.MMNAM, ACCT1.mbr_lst_nam, Demographics.MNSUF, ACCT1.addr_line1, ACCT1.city, ACCT1.state, ACCT1.zip, ACCT1.mbr_ssn, CCT.Credit_Card_Number
FROM SQLUser.CCT CCT, SQLUser.Demographics Demographics, SQLUser.ACCT1 ACCT1
WHERE Demographics.acn = ACCT1.acn AND CCT.acn = Demographics.acn AND CCT.acn = ACCT1.acn
The question is, how exactly can I create an outer join so that I return ALL accounts from any table? If I remove the joins wouldn't I just get redundant information?
SELECT ACCT1.acn1, ACCT1.mbr_1st_nam, Demographics.MMNAM, ACCT1.mbr_lst_nam, Demographics.MNSUF, ACCT1.addr_line1, ACCT1.city, ACCT1.state, ACCT1.zip, ACCT1.mbr_ssn, CCT.Credit_Card_Number
FROM SQLUser.CCT CCT, SQLUser.Demographics Demographics, SQLUser.ACCT1 ACCT1
WHERE Demographics.acn = ACCT1.acn AND CCT.acn = Demographics.acn AND CCT.acn = ACCT1.acn
The question is, how exactly can I create an outer join so that I return ALL accounts from any table? If I remove the joins wouldn't I just get redundant information?
ASKER
Thanks for the response.
When I try that (after changing table names back), I get
[SQLCODE: <-23>:<Label is not listed among the applicable tables>]
[Cache Error: <<Syntax?errdone_2%qaqqt>]
[Details: <Prepare>]
[%msg: < SQL ERROR #23: Label 'ACCT1' is not listed among the applicable tables^SELECT ACCT1.acn1 ,>]
Again, I changed the ACCT1 back to the actual name, and verified that I changed everything to how it should be.
When I try that (after changing table names back), I get
[SQLCODE: <-23>:<Label is not listed among the applicable tables>]
[Cache Error: <<Syntax?errdone_2%qaqqt>]
[Details: <Prepare>]
[%msg: < SQL ERROR #23: Label 'ACCT1' is not listed among the applicable tables^SELECT ACCT1.acn1 ,>]
Again, I changed the ACCT1 back to the actual name, and verified that I changed everything to how it should be.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SELECT ACCT1.acn1, ACCT1.mbr_1st_nam, Demographics.MMNAM, ACCT1.mbr_lst_nam, Demographics.MNSUF, ACCT1.addr_line1, ACCT1.city, ACCT1.state, ACCT1.zip, ACCT1.mbr_ssn, CCT.Credit_Card_Number
FROM
SQLUser.CCT CCT
FULL OUTER JOIN SQLUser.Demographics Demographics ON CCT.acn = Demographics.acn
FULL OUTER JOIN SQLUser.ACCT1 ACCT1 ON Demographics.acn = ACCT1.acn AND AND CCT.acn = ACCT1.acn