How do I join tables A and B (see pic) on TripID and where TimeTriggered is between SegmentStart and SegmentEnd? I could try: However since BETWEEN is inclusive on both ends it would join alarm B to both segment 1 and 2. I could try >= AND < but it would leave out alarm C. I could try > AND
Postgres recursive queries and window functions to produce tree from table
Let’s say I have the following data. I got the request to produce a table with a single column in which categories and subcategories appear arranged like this. I don’t understand how to complete the task with the requirements established by my client (especially the first one). These are: I should…
SQL not using ALIAS column for calculation
Question Statement – From the given trips and users tables for a taxi service, write a query to return the cancellation rate in the first two days in October, rounded to two decimal places, for trips not involving banned riders or drivers. Question code on Oracle SQL. My Solution Code is below. However,…
Subquery on SELECT with conditional WHERE clause after it
So I have three tables, news : topics : and news_topics : And I want to return it like these : I succeed on returning that value using this query But there is one flaw to that query, on the above result there are one row that contains null topics. When I add AND topics IS NOT NULL after it
Is there any syntactic sugar to using group by columns 1,2,3,…,n in SQL?
I know it isn’t a “good way of doing things”, but we use the following notations in many of our SQL queries : I was wondering if there is an even more succinct way of writing it, e.g. GROUP BY 1-n (Group by col_1 to col_n) Answer Assume you have table with dozen or more columns Consider belo…
PostgreSQL select record with multiple condition
I am trying to select the record but not getting the record I wanted. here is my sqlfiddle http://sqlfiddle.com/#!17/5296a/1 so when I selects the record using below query it gives me result with the title DDDDD. it should give me GGGGGGGG. I wanted to check if there is any record with author_id=3 and greater…
How can I get a value when the master table of the outer join is empty?
I have created a table where I get the sum of each item. However, this query returns : 0 row affected. I know what the problem is. If the key_id and cust_id are not in the t1 table specified as an alias, the value cannot be returned because the t1 table is empty. If there is data for key_id in
Delete unused groups in Laravel 8
I have a group model and I want to delete groups that don’t have any member. How can I get empty groups with eloquent or SQL query ? And this is the User model code: Answer I think whereDoesntHave work in you situation.
SQL queries for calculating shipping charges based on subtotal amount [closed]
Closed. This question needs details or clarity. It is not currently accepting answers. Want to improve this question? Add details and clarify the problem by editing this post. Closed 1 year ago. Improve this question ID Name min_amount charge 1 Standard 50.00 3.00 2 Express 50.00 5.00 3 Standard 100.00 2.00 4…
How can I make the trigger return a value?
I have a ‘roomstate’ table that represents states that I keep updated. This is what happens when the employee assigns room 2 to customer 1 whose booking_id is 2. As the table above is continuously updated, the existing contents may become NULL again. I’d like to have a record of updating thi…