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.
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.
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.
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?
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
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?