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 4 1227
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
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
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
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Chris |
last post by:
I know the reason why I get an error when the fields are set to null, but I'm working with old data that contains them. Any quick fixes? was hoping to avoid writing out every field in sql statement...
|
by: David Van D |
last post by:
Hi there,
A few weeks until I begin my journey towards a degree in Computer
Science at Canterbury University in New Zealand,
Anyway the course tutors are going to be teaching us JAVA wth bluej...
|
by: Bart |
last post by:
Could someone explain me what is wrong with this code ?
I gives me a compile error:
Error 1 Use of unassigned local variable 'fileStreamObject' C:\Documents and
Settings\Bart\Local...
|
by: DolphinDB |
last post by:
Tired of spending countless mintues downsampling your data? Look no further!
In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
by: Vimpel783 |
last post by:
Hello!
Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
|
by: jfyes |
last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
by: Shællîpôpï 09 |
last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
| |