I want to use with clause in order to shorten column names which will be used for calculations whenever I add with clause block of code to my SQL query it gives error: a column may not be outer-joined to a subquery, I’m not even using outer join, what’s the issue? Answer Didn’t test anything…
Tag: subquery
Find departments with no employees
We were given an assignment in class to provide at the minimum two different solutions to find departments, which don’t have any employees. As you can see from below I completed the task successfully. An additional solution is extra credit, which I like to get. Unfortunately, I can’t think of a th…
Error “more than one row returned by a subquery used as an expression”
I have the following 3 tables: airport area booking And I have this query Running the query I get the error: I don’t understand what the problem is. I added an SQL Fiddle to play with: http://sqlfiddle.com/#!17/8a09f/1 Answer A subquery in the SELECT list is only allowed to return a single value. Not mu…
How do I used distinct to remove duplicates in this query?
I have the following table: Table I am trying to write a query (that I will include in another query) to display how many account numbers there are per symbol. I wrote the following query: The query displays like this but it counts the same account number multiple times per symbol. EURUSD should have 3 and US…
SUBSELECT (SQL)
I have to tables. The first table (customers) shows data about the customers (id, firstname, lastname). The second table shows data about the orders (timestamp and customer id). So, i wanted to see the last order by a customer in my table “customers” and did a SUBSELECT and it worked. But, there a…
SQL: Search two strings in the same column through subquery
I have three tables using an Oracle database: The mix table interconnects Recipe and Ingredients. I am supposed to find the one cocktail made by the barman Otto which utilizes ingredients from Spain and Cuba. This is what I have tried until now: Expected Output from this query: At the moment, I am just gettin…
How to use column derived from SELECT subquery elsewhere in SQL query
I have two database tables that look something like this: Context: Booking_Statuses is used to retain a history of the various stages that a booking can go through, such as PENDING or ACCEPTED, so I can construct a timeline of events for the booking. One Booking has many Booking_Statuses. I’m using Post…
Oracle: WITH-clause querying two tables
Using an Oracle database, I have two tables: I am supposed to find – using the WITH-clause – all the employees who currently work in the same department where they started to work (hire_date = start_date and same department_id). I easily got the right result using a JOIN in the subquery: (Right) O…
Python Script to run SQL query UPDATE statement to loop through each row in result set and update columns
Newby working on my first project. Sorry for this explanation. I have 2 x tables: t1: master table with single rows (unique project-id) and 3 status fields, s1,s2,s3 t2: list table with repeating project_id’s with 3 status fields s1,s2,s3 (and other data not relevant here). The value in the s1-3 fields …
Sum of Current + Previous X Nth Rows
I am looking to find the sum of the current + the last X Nth Rows. I am able to do this with the following query, however it is not very scalable. If this example, I’m finding the current value of “amount”, plus the last 3 “amounts” split 6 apart: X = 3 N = 6 I will be using