Hi, I was hoping someone could confirm the following about the
following number 2 SQL query:

1. This extracts the data required given that there is a recording to
match the records

2. This should, but im not convinced extract all the remaining records
that do not have a matched recording, but fulfil the rest of the
criteria. If not please help...

3. As the two querys produce the same layout of tables if I did a UNION
ALL, this should work shouldnt it?

Query 1:
------------
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
l.last_call_date between #04/24/2006 12:00 AM# and #04/25/2006
11:59 PM# 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

ORDER BY ai.agent_login, ai.entry_date

Query 2.
-------------
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,
'' 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
l.last_call_date between #04/24/2006 12:00 AM# and #04/25/2006
11:59 PM# AND
count
(
SELECT rec.*
WHERE 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
) < 1

ORDER BY ai.agent_login, ai.entry_date

Thanks in advance for any help, its greatly appreciated.

David

David (david.goodyear (AT) gmail (DOT) com) writes:
These conditions seems to contradict each other.

But why? Why not an outer join instead?

An EXISTS clause is what you want. This is odd and less effiecient.


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) 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

Hi,

Is there a NOT operator in sql? I actually want NOT EXISTS as i believe
these two statements would get a complete data set. One gets the
records which also have a recording match, and then i want to get all
without. After this i was going to union the two sets together.

Thanks for the help

David

Erland Sommarskog wrote:

David (david.goodyear (AT) gmail (DOT) com) writes:
Yes, NOT EXISTS is a common operation. This query returns the two
customers in the Northwind database that does not have any orders:

SELECT C.CustomerID, C.CompanyName
FROM Customers C
WHERE NOT EXISTS (SELECT *
FROM Orders O
WHERE O.CustomerID = C.CustomerID)


I believe the Northwind database is available in Access as well. The
syntax should work in Access as well.


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) 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

On 26 Apr 2006 07:50:47 -0700, David wrote:

Hi David,

Yes. NOT EXISTS (subquery) is valid syntax.

--
Hugo Kornelis, SQL Server MVP