Please excuse this Access question on the CF list; this query concerns a
CF site and I've run out of places to ask!
I'm having a heck of a time with a complex query. I can't seem to get
outer joins to work in Access, either using the *= syntax or the
LEFT OUTER JOIN ... ON syntax. The same query will work either way
against the same database running on a SQL Server 7 db. Unfortunately,
this query needs to run against the Access version of the DB due to
ISP hosting constraints.
Our data model calls for storing bits of information (actually a mailing
list) as rows related to a master row in another table, data that
normally would be stored in fields in the master row. Problems arose
when we noticed that a couple of fields were null in many of the
entries, necessitating the outer joins to get the data we want.
The query involves three tables:, RS_link (the master table),
subcategories (a table that stores a "type" characteristic for rows in
RS_Link), and data (a table that stores attributes related to a row in
RS_Link each in a separate row). To produce a single record per RS_Link
entry requires joins of several aliased copies of the data table, one
column extracted from the data table according to each alias. The idea
was to join RS_Link and subcategories together first, then tack on
an aliased data table for every column needed via an outer join.
Any thoughts would be greatly appreciated!
Here's the query:
-------------------------------------------------------------------
SELECT D1.dt_text AS FirstName, D2.dt_text AS LastName,
D3.dt_text AS Address1, D4.dt_text AS Address2,
D5.dt_text AS City, D6.dt_text AS State,
D7.dt_text AS Zip, D8.dt_text AS Country,
D9.dt_text AS Phone, D10.dt_text AS AltPhone,
D11.dt_text AS Fax, D12.dt_text AS Email
FROM
((((((((((((subcategories AS S INNER JOIN rs_link AS RS ON
RS.subcat_id=S.subcat_id)
LEFT JOIN data AS D1 ON RS.r_id=D1.r_id)
LEFT JOIN data AS D2 ON RS.r_id=D2.r_id)
LEFT JOIN data AS D3 ON RS.r_id=D3.r_id)
LEFT JOIN data AS D4 ON RS.r_id=D4.r_id)
LEFT JOIN data AS D5 ON RS.r_id=D5.r_id)
LEFT JOIN data AS D6 ON RS.r_id=D6.r_id)
LEFT JOIN data AS D7 ON RS.r_id=D7.r_id)
LEFT JOIN data AS D8 ON RS.r_id=D8.r_id)
LEFT JOIN data AS D9 ON RS.r_id=D9.r_id)
LEFT JOIN data AS D10 ON RS.r_id=D10.r_id)
LEFT JOIN data AS D11 ON RS.r_id=D11.r_id)
LEFT JOIN data AS D12 ON RS.r_id=D12.r_id
WHERE S.area_id=2 And
D1.q_id=34 And
D2.q_id=35 And
D3.q_id=20 And
D4.q_id=21 And
D5.q_id=23 And
D6.q_id=22 And
D7.q_id=24 And
D8.q_id=25 And
D9.q_id=26 And
D10.q_id=36 And
D11.q_id=28 And
D12.q_id=27;
-------------------------------------------------------------
Thanks,
Brian
_____
Brian Panulla [EMAIL PROTECTED]
Elmwood Media Group 814.238.6680 www.elmwoodmedia.com
------------------------------------------------------------------------------------------------
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message
with 'unsubscribe' in the body to [EMAIL PROTECTED]
