Suppose I have the following table where there are two sets of observation dates (2015-01-01, 2016-01-01) in the first column. For each observation date, there are associated item_date and item_value. observation_date item_date item_value 2015-01-01 2012-12-31 0 2015-01-01 2013-03-31 1 2015-01-01 2013-06-30 2…
Tag: sqlite
How to retrieve all grand parents at once
I have two simple tables in SQLITE3 (family bonds): “persons” (id, fname, lname) “relationships” (parent/child) I would like to get each grand children along with all their grand parents (from 1 to 4 of them depending on the grand child) so that 1 row result = Thanks to Caius Jard, I&#…
Find two values of one Column based on value of another column per ID
I have a sqlite db with one table that called Loan. This table with sample data is here: Loan Table at sqlfiddle.com This table contains below Columns: Now, I need a query to show desired result, contain [empid],[Codepayid],[Lval-1],[Lval-2],[Sum(Lint)],[Lrmn-1],[Lrmn-2], With this Conditions: For example: Re…
Exclude all from attributes where employee table does not have connection with in the pivot table
I need to exclude all attributes that have no connection with employee1 in Pivot table. in this case attribute3. What is the SQL query for that? Answer Use not exists:
fetchall() not working even with values in database
I am working in python with sqlite3 and I am trying to create a database. I created a table: c.execute(“CREATE TABLE persoane (nume text, varsta integer, salariu integer)”) Then I added the values into the table: c.execute(“INSERT INTO persoane VALUES(‘David’, 16, 1000)”) W…
Convert Date format in SQLite Database
I have a SQLite Database and I mistakenly loaded a lot of data with mismatching date formats. There are dates formatted as: MM/DD/YYYY and dates formatted as: DD-Month Abbr. – YY. I would really like them all to be YYYYMMDD. Is there a way to reformat all of the dates to match the preferred format? Than…
Finding date-streak in SQL
I got my sqlite table with entries having a DateTime field. I want to check if there are entries with n consecutive days. Like a streak. Now I could just query all entries ordered by the date-field and iterate over them and check by myself. I am just wondering if there is some more efficient way to do this. L…
What’s the scope of table aliases?
This question is in the context of Sqlite but I wonder in general what’s the rule for aliases. Suppose I have the following query: The question is, will the two t1’s and t2’s clash? in my experience with multiple database vendors they won’t clash, but, could it confuse the optimizer to…
SQL Filter by dateTime
day is a integer variable. If I set the day value to 1, database is filtering for 24 hours. But if I set the day value to 1, I only want to see the logs of the new day. How can I do that? Thank you for your help. Answer
SQLite update data from one table to another
I’m trying to insert some data from one SQLite database to another. I have 2 tables called “members” and “attendance.” What’s supposed to happen is, everytime a new member is added into the member table (or removed), the name and ID of the specific member should also be add…