UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Query Help, Any Version    
 
   
Kamulegeya
post Sep 14 2018, 03:02 AM
Post#1



Posts: 1,828
Joined: 5-September 10
From: Kampala,Uganda The Pearl of Africa


Hello Members,

I have two tables tblBatches(BatchID and other fields) and tblBatchLines (LineID,BatchID,Datesmssent(date sms sent)).

So i have a routine which sends sms and update the field date sms sent . Some lines in a batch might not be updated.

I want to create a query which returns BatchIDs where none of the line is updated as sms sent.

I have tried

CODE
select b.BatchID from dbo.tblBatches as b

where b.BatchID not in(select BatchID from dbo.tblBatchLines  as a where a.datesmssent is null)


The wrong records are returned(Including those with updated datesmssent field).

Any suggestions?

Ronald
Go to the top of the page
 
JonSmith
post Sep 14 2018, 03:15 AM
Post#2



Posts: 3,843
Joined: 19-October 10



Why are you using a Sub query in your WHERE criteria?

Just use a normal SELECT query.
I'd also advocate against using meaningless aliases like 'a' and 'b' but do know some people think thats a good thing, for some reason.
Go to the top of the page
 
isladogs
post Sep 14 2018, 03:17 AM
Post#3



Posts: 481
Joined: 4-June 18



If I understand you correctly this doesn't need a subquery.
Create a new query joining both tables using BatchID and filter where datesmssent field is not null.

BTW you have 2 identical threads. Can you remove the text from the other one.

EdIT Too late. You now have a similar answer in both threads.
This post has been edited by isladogs: Sep 14 2018, 03:20 AM

--------------------
nil illigetimi carborundem est
Go to the top of the page
 
Kamulegeya
post Sep 14 2018, 03:33 AM
Post#4



Posts: 1,828
Joined: 5-September 10
From: Kampala,Uganda The Pearl of Africa


Hi, It submitted twice...i dont know how..

But creating a query does not work since it filters by line.

May be i change the date sent field to be on the Batch table.

Ronald
Go to the top of the page
 
cheekybuddha
post Sep 14 2018, 03:35 AM
Post#5


UtterAccess VIP
Posts: 10,352
Joined: 6-December 03
From: Telegraph Hill


Hi Ronald,

You don't even need to join tables:
CODE
SELECT BatchID
FROM dbo.BatchLines
GROUP BY BatchID
HAVING COUNT(datesmssent) = 0;


hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
Kamulegeya
post Sep 14 2018, 04:12 AM
Post#6



Posts: 1,828
Joined: 5-September 10
From: Kampala,Uganda The Pearl of Africa


David,

It is awesome from you.

uarulez2.gif

Ronald
Go to the top of the page
 
cheekybuddha
post Sep 14 2018, 06:19 AM
Post#7


UtterAccess VIP
Posts: 10,352
Joined: 6-December 03
From: Telegraph Hill


Ronald, as always,

yw.gif

d

--------------------


Regards,

David Marten
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    26th September 2018 - 06:18 AM