FAQ Database Discussion Community


Error when using WHERE in Correlated Subquery

sql-server,subquery,where-clause,correlated-subquery
I have the following relationship: Bank -> Financing -> Contracts -> Supplier I have to select all the Banks that is related to a Supplier, using the query below: SELECT DISTINCT A.Name AS BankName, A2.Name as SupplierName FROM Companies A INNER JOIN Financing F ON F.IdFinancialCompany = A.Id INNER JOIN...

How to merge these queries into 1 using subquery

sql,subquery,correlated-subquery
Select * from HotelPerson Where RoomID IN (select ID from HotelRoom Where BookingID = 36 ) Select * from HotelCancelationPolicy Where RoomID IN (select ID from HotelRoom Where BookingID = 36 ) How can I merge these both queries into 1 query ?...

Optimize slow correlated query in Oracle SQL

sql,oracle,correlated-subquery
I made a query which works, but is a little slow in my opinion. When I suppress the output to 10 rows, it takes 13 minutes to perform the query. This is the query, stripped from some stuff: SELECT (SELECT ANSWER FROM ( SELECT to_number(fiit.ANSWER, '999') ANSWER, foin.CLIENT_ID id, foin.STARTDATE...

Want a count but it repeats 1 with every record

sql,sql-server,subquery,correlated-subquery,inner-query
I want a count but it repeats 1 with every record. Can you please suggest what to do? SELECT Count(*), innerTable.* FROM (SELECT (SELECT NAME FROM tours WHERE tours.id = tourbooking.tourid) AS NAME, (SELECT url FROM tours WHERE tours.id = tourbooking.tourid) AS Url, (SELECT TOP 1 NAME FROM tourimages WHERE...

MYSQL with multiple SUBQUERIES

mysql,correlated-subquery,in-subquery
Hi and thanks for reading. SELECT DISTINCT (thisID and thisNAME) from table1 WHERE thisID IN (SELECT id from table2 WHERE ... condtions) OR (SELECT id from table3 WHERE ... different condtions) OR (SELECT id from table99 WHERE ...even more conditions) I need to perform a few SELECTS to provide a...

Oracle ORA:00904: Subquery in LEFT JOIN

sql,oracle,subquery,correlated-subquery
I'm aware of the subquery limitations of Oracle's ANSI SQL setup. You can't use an identifier in a subquery that is declared more than one level deep. I'm attempting the following query, which as far as I can see is only one level deep, but I'm getting this error. Does...

Correlated subqueries: How to get the ID of the outer query?

sql,sum,subquery,correlated-subquery
I have multiple tables : Person --------------------- IDPerson, Name StickerTransaction --------------------- IDTransac, IDPerson, IDSomeoneElse, NbStickersReceived, NbStickersGiven Purchase --------------------- IDPurchase, IDPerson, NbStickersBought I'm trying to get every person who never made a transaction or currently have 0 sticker. To have the number of stickers a person has, here's the formula :...

Getting the multiple count() in a sinle select statement without script repitiion

sql,database,db2,correlated-subquery
Below is a script to get the count in a single select with lot of code redundancy. How really I can clean up the code? SELECT (SELECT COUNT(CHH.TRANSACTION_TYPE) FROM HW_PROD.ILCCHH CHH INNER JOIN HW_PROD.ILCOHD OHD ON CHH.CONTROL_NUMBER = OHD.CONTROL_NUMBER AND CHH.PICK_TICKET = OHD.TICKET_NUMBER AND CHH.CHHDC = OHD.OHDDC AND CHH.WHSE_IDENTITY =...

MySQL Subquery related to LEFT JOINS

mysql,subquery,correlated-subquery
I was given the task of extracting customer information from a database and I am stuck on the last part! I hope my explanation is sufficient to describe my problem and attempts. Thank you sincerely for taking the time to even look at this! Goal: Return one row per customer...

How do I improve the performance of a MySQL query that has a dependent subquery that isn't?

mysql,performance,correlated-subquery,mysql-dependent-subquery
I am working with a table in MySQL that defines a tree hierarchy using the "adjacency list" method, which should be fine for my purposes. I need to compute the maximum of some value over all children using the (fast) query SELECT MAX(N.someValue) AS rate FROM `nodes` N WHERE N.parent...

SQL subquery (stockin quantity runtime by sum up the stock in and stock out group by store and than subtract from stockin to stock out)

sql,switch-statement,subquery,inner-join,correlated-subquery
I have 2 tables stockin and stockout i want to calculate stockin quantity runtime by sum up the stock in and stock out group by store and than subtract from stockin to stock out .. My query is working well but when it doesnt found any record in stock out...

Is there a more efficient way to execute this nested SQL query?

sql,ms-access,odbc,subquery,correlated-subquery
I am writing a query to fill a select box on a user form and it works fine but when I look at the SQL, I feel like there should be a better, more efficient way to write it. Notice the two nested SELECTs below are from the same table....

Two columns into One single cell SQL query

sql,sql-server,subquery,correlated-subquery
Select COALESCE( (Select Convert(nvarchar(20),Count(ID))+' '+'Adult(s) - ' From TourPerson Where BookingID = 1 And [Type] = 1), (Select Convert(nvarchar(20),Count(ID))+' '+'Child(s)' From TourPerson Where BookingID = 1 And [Type] = 2) ) as TotalPassengers I want to take it in below mentioned format 1 Adult - 0 Children now it is...

Splitting Subqueries into Seperate Queries is more efficient?

java,mysql,performance,correlated-subquery,detachedcriteria
I've a subquery like this. Basically I've used multiple detached criteria to form multiple subqueries. SELECT this_.id AS y0_, this_.a AS y1_, this_.b AS y2_, this_.c AS y3_, this_.d AS y4_, this_.e AS y5_ FROM table_a this_ WHERE this_.id IN ( SELECT this_.a AS y0_ FROM table_b this_ WHERE this_.b=?...