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 MarketingCampai gn,
ai.agent_input2 as ProductsSold,
ai.first_name as Cust_FirstName,
ai.last_name as Cust_LastName,
ai.agent_input1 as Cust_PersonalNu mber,
ai.street_addre ss as Cust_AddressStr eet,
ai.city as Cust_AddressCit y,
ai.state as Cust_AddressSta te,
ai.zip as Cust_AddressZIP ,
rec.file_name as AgreementRecord ingFile

FROM agent_input ai,
leads l,
recordings rec

WHERE ai.whole_phone_ number = l.whole_phone_n umber AND
l.call_status = 1110 AND
l.last_call_dat e between #04/24/2006 12:00 AM# and #04/25/2006
11:59 PM# AND
rec.whole_phone _number = l.whole_phone_n umber 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 MarketingCampai gn,
ai.agent_input2 as ProductsSold,
ai.first_name as Cust_FirstName,
ai.last_name as Cust_LastName,
ai.agent_input1 as Cust_PersonalNu mber,
ai.street_addre ss as Cust_AddressStr eet,
ai.city as Cust_AddressCit y,
ai.state as Cust_AddressSta te,
ai.zip as Cust_AddressZIP ,
'' as AgreementRecord ingFile
FROM agent_input ai,
leads l,
recordings rec

WHERE ai.whole_phone_ number = l.whole_phone_n umber AND
l.call_status = 1110 AND
l.last_call_dat e 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_n umber 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]

Re: Please help quick statement check

David (david.goodyear @gmail.com) writes:[color=blue]
> 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...[/color]

These conditions seems to contradict each other.
[color=blue]
> 3. As the two querys produce the same layout of tables if I did a UNION
> ALL, this should work shouldnt it?[/color]

But why? Why not an outer join instead?
[color=blue]
> count
> (
> SELECT rec.*
> WHERE rec.whole_phone _number = l.whole_phone_n umber AND
> rec.last_name = l.last_name AND
> rec.agent = ai.agent_login AND
> rec.campaign = l.campaign
> ) < 1[/color]

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


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[David]

Re: Please help quick statement check


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:[color=blue]
> David (david.goodyear @gmail.com) writes:[color=green]
> > 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...[/color]
>
> These conditions seems to contradict each other.
>[color=green]
> > 3. As the two querys produce the same layout of tables if I did a UNION
> > ALL, this should work shouldnt it?[/color]
>
> But why? Why not an outer join instead?
>[color=green]
> > count
> > (
> > SELECT rec.*
> > WHERE rec.whole_phone _number = l.whole_phone_n umber AND
> > rec.last_name = l.last_name AND
> > rec.agent = ai.agent_login AND
> > rec.campaign = l.campaign
> > ) < 1[/color]
>
> An EXISTS clause is what you want. This is odd and less effiecient.
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.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[/color]

[Erland Sommarskog]

Re: Please help quick statement check

David (david.goodyear @gmail.com) writes:[color=blue]
> 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.[/color]

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@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Hugo Kornelis]

Re: Please help quick statement check

On 26 Apr 2006 07:50:47 -0700, David wrote:
[color=blue]
>
>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.[/color]

Hi David,

Yes. NOT EXISTS (subquery) is valid syntax.

--
Hugo Kornelis, SQL Server MVP