Need Help with SQLite Query

Databases
#1

Hi,
I need help in building a SQLite Query:

In my DB I do store “Start_Campaign” and a “End_Campaign” as DATE.

I want to check, if in a specific period is no campaign saved.

this is my string:

SELECT ID FROM Bestellungen WHERE DisplayIDs LIKE '%4|%' AND ((Start BETWEEN '01.02.2014' AND '22.02.2014') OR (Ende BETWEEN '01.02.2014' AND '22.02.2014') OR (Start < '01.02.2014' AND Ende > '22.02.2014'));

problematic is the last row [quote]OR (Start < ‘01.02.2014’ AND Ende > ‘22.02.2014’))[/quote]. This do not work.

Here is a pic, that shows what I want:
https://dl.dropboxusercontent.com/u/92235674/QueryOverview.png

I hope it was understandable what I want

#2

I think this is probably not working because of your date formats. For things like this to work properly you need to be using Date-type fields, and you should be querying with dates of the format 2014-02-01. If you do that I’m pretty sure it’ll work, but it’s hard to know without seeing the design of your database.

#3

well thanks,
in Germany we use another dateformat (DD.MM.YYYY). I’d never worked before with dates.

I test it and hope it solves my problem!

Is there set a specific format in SQLite? I want to avoid that the localisation of the Webservers can change how the DB works.

#4

See 1.2 in here - http://www.sqlite.org/datatype3.html - there’s not a specific date type, so store it as text; use the ISO standard (“YYYY-MM-DD HH:MM:SS.SSS”). RealStudio has the concept of both reading and writing SQLdates from date objects, which will help you.

dim d as date = New Date msgBox d.sqlDate

#5

(see also d.sqldatetime)

#6

WORKS!

Thanks!

I’ve got another SQL-question:

I want to join two Tables “Kunden” and “Bestellungen”.
I do store in “Bestellungen.KundenID” the IDs of “Kunden.ID”. There are several records in “Bestellungen”, of one record in “Kunden”.

This is my String:

SELECT Bestellungen.Start, Kunden.Name FROM Kunden INNER JOIN Bestellungen ON Bestellungen.KundenID = Kunden.ID

I only get the last record in “Bestellungen”. What is my mistake?

#7

The inner join. That will return you one row in Bestellungen per row in Kunden which matches. If you want multiple matches you will need an outer join. LEFT JOINs join OUTER implicitly.

Try

SELECT Bestellungen.Start, Kunden.Name FROM Bestellungen LEFT JOIN Kunden ON Bestellungen.KundenID = Kunden.ID

If you have any rows in Kunden which do not have a corresponding Bestellengun row then they will have NULLs returned; if you don’t want that then you need to remove them as follows:

SELECT Bestellungen.Start, Kunden.Name FROM Bestellungen LEFT JOIN Kunden ON Bestellungen.KundenID = Kunden.ID WHERE Bestellungen.Start IS NOT NULL

#8

Hey,

still not working :frowning:

https://dl.dropboxusercontent.com/u/92235674/SQLite%20Test.png

#9

Hamish reversed the direction, probably by mistake.

SELECT Bestellungen.Start, Kunden.Name
FROM Kunden
LEFT JOIN Bestellungen ON Bestellungen.KundenID = Kunden.ID
#10

Indeed; sorry for the slip.

#11

Hmm,

no, not working:
https://dl.dropboxusercontent.com/u/92235674/SQLite%20Test%202.png

:frowning:

Thanks guys!

#12

As I said:

So use

SELECT Bestellungen.Start, Kunden.Name FROM Kunden LEFT JOIN Bestellungen ON Bestellungen.KundenID = Kunden.ID WHERE Bestellungen.Start IS NOT NULL

#13

Yes, I tried!
But get the same rusult like here:
https://dl.dropboxusercontent.com/u/92235674/SQLite%20Test.png
(only other query expression)

#14

I’ve just made a SQLite database here with the same basic structure and run that query and returned the expected three rows so I don’t know what’s going on, sorry. What happens if you just run it as a query, rather than putting it into a view?

#15

Works!
Seems that the view-Generator in the sqlite-studio is broken…

have to reinstall it. If I execute in my App it works!

Sorry guys!

And thank you very, very much!

#16