Lets say I have a list of employee names and I want to select all rows with those names: Perhaps Jane and Joe don’t exist in this table. How can I create a query that identifies items in my list that don’t exist at all. I’m not looking for any specific format as the returned result; it can b…
Tag: sql
Oracle SQL- Stack dates if they match diagonally
The goal is to stack dates if DT and VAR are same, and END_DATE matches the START_DATE of next highest START_DATE In the example, row 2 and 3 are combined because END_DATE of row 2 matches START_DATE of row 3. Original Expected I have tried the query below but the solution won’t work when there is addit…
Redshift Postgres 8
I’m trying to write a query to solve a logical problem using Redshift Postgres 8. Input column is a bunch of Order IDs and Step Group IDs and desired output is basically a sequence of the IDs as you can see in the screenshot. If you could help me answer this question, that would be great, thanks! This i…
How do I write a Ruby where clause with dates and array lookup?
I want to do something like the above. I first want to check if the model external id is in the array of appointment_ids, then make sure it is not soft deleted and then check if the record start time is between dates.. Any help much appreciated. Answer You don’t need to use a SQL string to create that q…
How to use an alias in Hive?
I am trying to find unique cities using the window function, I am not able to use an alias in this query Answer You cannot have a window function in the where clause. Put it in a subquery and do the filter afterwards:
~ operator in SQLITE
I’m running this part of a query on Postgres and it’s running fine But when I try to run it in SQLite, I get this error: near “~”: syntax error Do you have any idea how I can run this function in SQLite? Answer Actually, if you want columns that start with a digit, you can simply use: …
SQL query for checking whether a column with a specific value has its correspondent row with another specific value in the same column
I am not completely sure how to solve this problem. My data looks like this: And I would like to see whether L_TYPE 673 has no correct correspondent with L_TYPE = 660. I want to find out whether the column L_TYPE with the value 673 has also its correspondent 660. All the other values do not change. However, I…
Doing 4 way filter based on 3 tables using GORM
I’ve been trying to achieve a 4 way to join/filter based on 4 tables “Offers”, “UserPaymentMethods” and a junction table “OffersUserPaymentMethods” defined as below; So I want to filter “offers” based on payment_method_id because offer_id lives in offers_u…
MySql Join Tables With Sum Of A Column
I have 3 tables in total category with columns category_id and category_name server with columns server_id, category_id, server_name and server_url server_hit with columns id, server_id, hit_count, day_date Here in the server_hit table, I am storing how many times a web server is being accessed daily. And in …
Simplification of SQL Query
SQL is not my specialty. I have a working query that accomplishes what I need it to, but I know there has got to be a cleaner way of accomplishing my goal. The query should return: – Name – Date of …