how to select all elments if exist or not exist

Question

0.00/5 (No votes)

See more:

Dear all

i have two tables in the table channels three column ( channel_ID - ChannelName - ChannelsStatus) and in the second table SentLog three cloumn (Log_ID - Log_ChannelID - Log_Date) and relationship one to many between
Channels_Channel_ID=SentLog.Log_ChannelID

process is get channelID and do something then add it to SentLog Table in the current datetime

i want to select all channels.Channel_ID that ChannelsStatus=Active and Be older one hour

to specify datetime i use this this c# code to pass datetine in query

C#

DateTime dt=DateTime.Now.AddHour(-1).ToString(@"MM/dd/yyyy HH:mm:ss")

and this is my query

SQL

Select Channel_ID From Channels left join SentLog on Channels.Channel_ID=SentLog.Log_ChannelID and Log_Date < cast('12/02/2014  19:34:55 ' AS DATETIME) AND Channels.Channel_Status='Active'

Result : this query give me all channels that active and not active and not specific the date condition

Moved by PIEBALDconsult

channel table have four recorde

channel_ID ChannelName ChannelsStatus
1          r1          Active
2          r2          Active
3          r3          Active
1          r4          DisActive

Sentlog table have 3 records

Log_ID Log_ChannelID Log_Date
1      1             02/12/2014 10:36:11 pm
2      1             02/12/2014 10:37:11 pm
3      1             02/12/2014 10:38:11 pm

and if the current datetime is 02/12/2014 10:38:11 pm

i want to select all channelID that active and be older 1 hour
in this example it must give me channelsid 2,3

Posted 2-Dec-14 8:34am

beljk

Updated 2-Dec-14 10:06am

PIEBALDconsult

v3

Add a Solution

Comments

PIEBALDconsult 2-Dec-14 16:24pm

So, maybe you want an INNER JOIN ?

4 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2014-12-02T08:54:00

Solution 2

Try this:

SQL

SELECT Channel_ID
FROM Channels AS Ch LEFT JOIN SentLog AS Sl ON Ch.Channel_ID=Sl.Log_ChannelID
WHERE Sl.Log_Date < @myDate AND Ch.Channel_Status='Active'

Now pass date as a SqlParameter[^].

For further information, please see:
WHERE[^]
Visual Representation of SQL Joins[^]

Posted 2-Dec-14 8:54am

Maciej Los

Comments

PIEBALDconsult 2-Dec-14 15:35pm

I still think the second AND needs to be replaced with WHERE; not the first.
But of course a proper parameter is also required.

Maciej Los 2-Dec-14 16:37pm

I'm wondering why? The result and the time of query execution should be the same (almost).

PIEBALDconsult · Answer 2 · 2014-12-02T08:55:00

Solution 3

Try replacing the second AND with WHERE

Select Channel_ID From Channels left join SentLog on Channels.Channel_ID=SentLog.Log_ChannelID and Log_Date < cast('12/02/2014  19:34:55 ' AS DATETIME) AND WHERE Channels.Channel_Status='Active'

Posted 2-Dec-14 8:55am

PIEBALDconsult

Updated 2-Dec-14 8:57am

v2

Comments

Maciej Los 2-Dec-14 15:05pm

Why second AND? I'd suggest to replace first AND.

PIEBALDconsult 2-Dec-14 15:13pm

That's what I thought at first too.

beljk 2-Dec-14 15:13pm

after change and to where . it's give me all active channels but not make a datetime condition

PIEBALDconsult 2-Dec-14 15:15pm

Yeah, try replacing the second AND instead.

beljk 2-Dec-14 15:28pm

i replaced it and the same result .. maybe channelID inside the sentlog table more than 3 times with the same datetime but diffrient in seconds and when replace and with where and greeter the current datetime to check it's give me the first three rows for this channelID then i added group by then give me it but one time

PIEBALDconsult 2-Dec-14 15:31pm

Maybe, best not to convert the date to a string.

beljk 2-Dec-14 15:37pm

the same result
my conclusion is must give my the channelid because when greeter the date it give my this id three time and when change it again to the past , give my the id one time ..... so the date condition is work fine

PIEBALDconsult 2-Dec-14 15:39pm

Maybe we need to see some sample data. Use Improve question.

beljk 2-Dec-14 15:54pm

-- Content moved to Question

beljk · Answer 3 · 2014-12-02T23:36:00

Solution 4

SQL

Select Channel_ID From Channels where NOT EXISTS (select SentLog.Log_ChannelID from SentLog where SentLog.Log_ChannelID=Channels.Channel_ID AND exists(select SentLog.Log_ChannelID from SentLog where SentLog.Log_Date >cast('" + DateTime.Now.AddHours(-1).ToString(@"MM/dd/yyyy  HH:mm:ss") + "' AS DATETIME) ) )AND Channels.Channel_Status='Active'";

this is solution

Posted 2-Dec-14 23:36pm

beljk

Comments

Maciej Los 3-Dec-14 5:47am

How about query performance?
You're using EXISTS twice! It must be really slow...

yusuf_ahmed · Answer 4 · 2014-12-03T02:02:00

Solution 5

Try this

SQL

declare @currentdatetime smalldatetime


select * from channels
where channel_ID not in (select Log_ChannelID
                        from SentLog
                        where Log_Date >= DATEADD(hh,-1,@currentdatetime)
    and ChannelsStatus = 'Active'

Posted 3-Dec-14 2:02am

yusuf_ahmed