i'm A Newbie in SQL 1

[zaq888]

i'm using dataenvironment from vb to generate the sql query. my problem is i'm passing a date value from the datepicker.value and send it to the sql.

SELECT date, time FROM TData WHERE (date BETWEEN ? AND ?) ORDER BY date

? will read data from start.value and end.value(datepicker)

this is one more SQL statemant:

SELECT TUnit.[cs-username]
FROM TUnit LEFT JOIN TData ON TUnit.[cs-username]=TData.[cs-username]
WHERE TData.[cs-username] Is Null;

question: how can i join the two queries?

 

[DanChard]

This would probably do it:

SELECT TUnit.[cs-username]
FROM TUnit LEFT JOIN TData ON TUnit.[cs-username]=TData.[cs-username]
WHERE TData.[cs-username] Is Null
and (Tdata.date BETWEEN ? AND ?)
ORDER BY date;

(one post would have done)

 

[zaq888]

no data is displayed... something wrong i think

what i want to do is,
first select data from TUnit that range BETWEEN ? AND ?
This is the SQL command:

SELECT date, time, cs-username FROM TData WHERE (date BETWEEN ? AND ?) ORDER BY date

then from the result copy it to temp folder and from there do another sql satement like this:

SELECT TUnit.[cs-username]
FROM TUnit LEFT JOIN TData ON TUnit.[cs-username]=TData.[cs-username]
WHERE TData.[cs-username] Is Null;

but i don't know how to copy the first sql result to temp table/// anybody can help mee???

I'm Keep Studying.... please show the way...
Not Good in English

 

[SonOfEmidec1100]

Now I see the problem with previous solutions - try this untested solution - its late.

SELECT Tunit.date, TUnit.time, TUnit.cs-username
FROM TUnit LEFT JOIN (SELECT * FROM Tunit WHERE Tdata.date BETWEEN ? AND ?) As MyTempTbl ON TUnit.cs-username= MyTempTbl.cs-username
WHERE MyTempTbl.cs-username Is Null
ORDER BY Tdata.Date

 

[SonOfEmidec1100]

Ooops couple of mixups with table names - i said it was late
Anyway it should give you the general idea.

SELECT Tunit.date, TUnit.time, TUnit.cs-username
FROM TUnit LEFT JOIN (SELECT * FROM Tdata WHERE Tdata.date BETWEEN ? AND ?) As MyTempTbl ON TUnit.cs-username= MyTempTbl.cs-username
WHERE MyTempTbl.cs-username Is Null
ORDER BY Tunit.Date

 

[SonOfEmidec1100]

Sorry about the confusion - Had a good sleep but woke up with this still bugging me. So heres hopefully the correct statement - awaiting a second opion.

By combining the queries (with corrections as below) what you will get returned is a list of customers who DONT have any data between the two dates, in which case the returned date and time will always be null.

SELECT MyTempTbl.[date], MyTempTbl.[Time], TUnit.[cs-UserName]
FROM TUnit LEFT OUTER JOIN
(SELECT *
FROM Tdata
WHERE Tdata.date BETWEEN ? AND ?) MyTempTbl ON TUnit.[cs-UserName] = MyTempTbl.[cs-username]
WHERE (MyTempTbl.[cs-username] IS NULL)
ORDER BY MyTempTbl.[date]

 

[zaq888]

Thanks a lot SonOfEmidec1100...

It work....

This is what i'm looking 4...
this my sql..

SELECT TUnit.[cs-username]
FROM TUnit LEFT JOIN [SELECT * FROM Tdata WHERE Tdata.date BETWEEN ? AND ?]. AS MyTempTbl ON TUnit.[cs-username] = MyTempTbl.[cs-username]
WHERE (((MyTempTbl.[cs-username]) Is Null))
ORDER BY Tunit.[cs-username];

Thanks guy...

I'm Keep Studying.... please show the way...
Not Good in English

 

[zaq888]

how can i knew that the TUnit.[cs-username] is being not logged in for the 3 consecutive days? anyone can help me?

I'm Keep Studying.... please show the way...
Not Good in English