The follow tables I have are: CUSTOMERS (CustomerID, firstname, surname etc) ROOMS (RoomID, RoomType, Description) BOOKING (BookingID, CustomerID, Arrival Date, Departure Date, number of people, …
SQL : select values between multiple ranges
I have a table like the one below… ID Price ==== ====== 1 1000 2 2000 3 4000 4 5000 Now I need a query to select values between 500 and 1500 And between 2500 and 4500. i.e. like …
Create MySQL Tables with Ansible
I’m using ansible to manage a small mail server using ubuntu. I wanted to use ansible to create a database which I can do and also create users for the database(s) which I can do also. But I’m not …
jsonb query with nested objects in an array
I’m using PostgreSQL 9.4 with a table teams containing a jsonb column named json. I am looking for a query where I can get all teams which have the Players 3, 4 and 7 in their array of players. The table contains two rows with the following json data: First row: second row: How does the query have to lo…
how to split one row to many based on a comma separate column
here is my data in mysql table: name childid city 11,12,13 maintain 21,22,29 pool 33,39,100 I want to get data as the below format. So I can use it to join another tables. 11 city 12 …
Sqlite get max id not working (?)
Im using this: But my query is empty. I have also tried this (This one works): But obviusly my query only contains the max_id key. What am I doing wrong with the first one? Answer You need to add another level of select for the MAX, like this: A better approach would be to order by id in descending order,
SQL to retrieve parent-child relationship in parent-child order, from a self referencing table
I use the following query to retrieve the parent-child relationship data, from a table which is self referencing to the parent. — go down the hierarchy and get the childs WITH ChildLocations(…
How can a query select dates from only a specific academic year?
This may be simple, but I cannot figure out the correct and simplest way to query a table which contains a date col to return the rows in which the date belongs to the current academic year. Knowing that for academic year I mean the period from the 1st of September of a year to the 31st of august of
Get all matching records where IDs are joined to a comma separated list
I have two database table like below: Form Id Name Fields(varchar-255) FormFields Id Name InputType Sample data for Form Sample data for FormFields Now I write query as below: And I get only one record as below: But I want all the records of FormFields table whose Id is present in the Fields columns of Form t…
MySQL Query join last comment to topic
I have a topic and topic_comments table. I want to join them both. The join is on topic.id = topic_comments.parent_id. I want to show the topic with latest comment and order by latest comment createdate. And not show duplicate topics. Can anyone help me? So far I have this: This however doesn ‘t show to…