Simple JOIN, INTERSECT query

[David]

Hi,

We are in the process of buying a new server to run mssql. However
before this as a tempory fix to using a msaccess backend i believe
through odbc i need to address the following issue:

SELECT ai.entry_date as CallTime,
ai.agent_login as AgentsLogin,
ai.campaign as MarketingCampaign,
ai.agent_input2 as ProductsSold,
ai.first_name as Cust_FirstName,
ai.last_name as Cust_LastName,
ai.agent_input1 as Cust_PersonalNumber,
ai.street_address as Cust_AddressStreet,
ai.city as Cust_AddressCity,
ai.state as Cust_AddressState,
ai.zip as Cust_AddressZIP,
rec.file_name as AgreementRecordingFile
FROM agent_input ai, leads l, recordings rec
WHERE ai.whole_phone_number = l.whole_phone_number AND
l.call_status = 1110 AND
rec.whole_phone_number = l.whole_phone_number AND
rec.last_name = l.last_name AND
rec.agent = ai.agent_login AND
rec.campaign = l.campaign AND
last_call_date between #04/24/2006 12:00 AM# and #04/25/2006 11:59 PM#
ORDER BY ai.agent_login, ai.entry_date

I want to make the recordings entry optional so the same results come
out whether it matches a recording or not. If it does i want it to
populate the AgreementRecordingFile column above, if not just put a ''
as you would with '' as AgreementRecordFile.

Does anyone know how you can do this, in a access based database system
using SQL through i believe ODBC?

Thanks

David

[Erland Sommarskog]

David (da************@gmail.com) writes:

We are in the process of buying a new server to run mssql. However
before this as a tempory fix to using a msaccess backend i believe
through odbc i need to address the following issue:

SELECT ai.entry_date as CallTime,
ai.agent_login as AgentsLogin,
ai.campaign as MarketingCampaign,
ai.agent_input2 as ProductsSold,
ai.first_name as Cust_FirstName,
ai.last_name as Cust_LastName,
ai.agent_input1 as Cust_PersonalNumber,
ai.street_address as Cust_AddressStreet,
ai.city as Cust_AddressCity,
ai.state as Cust_AddressState,
ai.zip as Cust_AddressZIP,
rec.file_name as AgreementRecordingFile
FROM agent_input ai, leads l, recordings rec
WHERE ai.whole_phone_number = l.whole_phone_number AND
l.call_status = 1110 AND
rec.whole_phone_number = l.whole_phone_number AND
rec.last_name = l.last_name AND
rec.agent = ai.agent_login AND
rec.campaign = l.campaign AND
last_call_date between #04/24/2006 12:00 AM# and #04/25/2006 11:59 PM#
ORDER BY ai.agent_login, ai.entry_date

I want to make the recordings entry optional so the same results come
out whether it matches a recording or not. If it does i want it to
populate the AgreementRecordingFile column above, if not just put a ''
as you would with '' as AgreementRecordFile.

If I understand this correcly you need an outer join:
SELECT ai.entry_date as CallTime,
ai.agent_login as AgentsLogin,
ai.campaign as MarketingCampaign,
ai.agent_input2 as ProductsSold,
ai.first_name as Cust_FirstName,
ai.last_name as Cust_LastName,
ai.agent_input1 as Cust_PersonalNumber,
ai.street_address as Cust_AddressStreet,
ai.city as Cust_AddressCity,
ai.state as Cust_AddressState,
ai.zip as Cust_AddressZIP,
coalesec(rec.file_name, '') as AgreementRecordingFile
FROM agent_input ai
JOIN leads l ON ai.whole_phone_number = l.whole_phone_number
LEFT JOIN recordings rec ON
rec.whole_phone_number = l.whole_phone_number AND
rec.last_name = l.last_name AND
rec.agent = ai.agent_login AND
rec.campaign = l.campaign
WHERE l.call_status = 1110 AND
last_call_date >= '20060424' AND
last_call_date < '20060426'
ORDER BY ai.agent_login, ai.entry_date

Two disclaimers:

1) I don't know from which table last_call_date comes from. I've assumed
that it comes from agent_input or leads. If it comes from recordings,
the above query is not likely to be correct.

2) The syntax works on SQL Server, because that is all I know. If you want
syntax that works on Access, ask comp.databases.ms-access.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx