Please help quick statement check

[David]

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

[Erland Sommarskog]

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

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...
These conditions seems to contradict each other.
3. As the two querys produce the same layout of tables if I did a UNION
ALL, this should work shouldnt it?
But why? Why not an outer join instead?
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

An EXISTS clause is what you want. This is odd and less effiecient.
--
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

[David]

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 (da************@gmail.com) writes:

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...

These conditions seems to contradict each other.

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

But why? Why not an outer join instead?

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

An EXISTS clause is what you want. This is odd and less effiecient.
--
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

[Erland Sommarskog]

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

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.

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, 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

[Hugo Kornelis]

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

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.

Hi David,

Yes. NOT EXISTS (subquery) is valid syntax.

--
Hugo Kornelis, SQL Server MVP