Tagged Questions

SQL term used to describe when a `SELECT` statement is used as part of a larger SQL statement. The larger statement may be DML and is always found within brackets or parenthesis.

learn more… | top users | synonyms

0
votes
1answer
29 views

Select products and include all cities it belong to

I have 2 tables : PRODUCTS +----------+----------+----------+----------+ | id | title | content | city | +----------+----------+----------+----------+ CITIES +----------+----------+ | ...
0
votes
1answer
30 views

Identify faulty sub-query (subquery returned more than 1 value)

I am trying to resolve an issue with a query that fails with this error: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when ...
0
votes
4answers
90 views

Why does this query take 32 sec to execute?

I keep my history records in one table and my pertinent data in another. I want to grab only the files from myfiles that currently have a given status recorded in statuses (ie, the latest status is ...
0
votes
1answer
23 views

How to model efficient permission checks for a social network?

The goal is to build a facebook like permission system. So that users can grant other users access on objects based on a direct assignment (allow user X to see posts) or allow users to access them ...
-2
votes
0answers
25 views

Find the salary history of employee ‘Margareta Markovitch’ [closed]

i want to know how to write the query for this question. Find the salary history of employee ‘Margareta Markovitch’.
0
votes
1answer
25 views

Stacked Left Joins or Left Join inside a Subquery?

OK, this is a fun one. Designing a database for a gaming system. I have 3 tables that are relevant for the query I'm trying to work on right now. character_skill_log with the relevant fields of id, ...
1
vote
1answer
46 views

Oracle: summing column value with subquery?

Are there some restrictions in Oracle that disallows to have a WHERE clause that compares value of column+(subquery count(*))? If the subquery table is empty, count(*) doesn't yield to zero in this ...
0
votes
2answers
33 views

Subquery or multiple left joins, or both?

I have two tables, for simplicity table1, with id as Primary key, and a field name, and table2 with the same. Each table has quite a few records, but during a complicated form process, the user is ...
0
votes
2answers
43 views

Using subqueries in delete query

I have a table candidate. It contains two columns name and roll. The values in all three rows of data are AA and 10. Now I want to delete only the second row. How can I do it?
0
votes
1answer
55 views

Alternative to sub query in MS Access

I'm looking into the possibilities for writing queries in MS Access, as my client currently uses Access and would be reluctant to move away to SQL Server. I've been using the data macros (which are ...
0
votes
2answers
51 views

Update specific records of table based on subquery's output (sql server)

have this query which produce below output : select sec.site,sec.Name,sec.Freq from ( select Name from sec group by name having count(Freq) > 3)as dt inner join sec on dt.Name = sec.Name where ...
0
votes
2answers
36 views

How are derived table implemented in MySQL

When MySQL makes a derived table as a result of SELECT (SELECT ...) etc or SELECT * FROM a JOIN (SELECT * from B) etc or SELECT * FROM (SELECT ...) Are these derived temporary tables created ...
0
votes
2answers
41 views

How to rewrite subquery to return only 1 row

I need to somehow check if query result is NULL, MySQL returns error Subquery returns more than 1 row. How should I rewrite it in order to work correctly please? SELECT DISTINCT user.id, ...
0
votes
1answer
28 views

Subquery on timestampdiff only allows for 1 result, how do I scale this for all results?

I'm currently writing up a query which is meant to output data to show curves of power profiles, and I'm facing a slight problem. So far, I've managed to do this: select datetime, PLANT_STATUS, ...
0
votes
1answer
32 views

SQLITE table joining with group

MESSAGE TABLE USERS msg_id | msg_to | msg_from | msg_body | msg_date user_id | user_name 1 1 2 hi 22/1/14 ...
0
votes
2answers
58 views

#1093 - You can't specify target table 'R' for update in FROM clause

As an attempt to answer Difference data between adjacent rows UPDATE Visits R SET Duration = TIMEDIFF( ( SELECT ReqTime FROM Visits N WHERE N.ID > R.ID AND N.Session=R.Session ORDER BY ID ...
0
votes
2answers
30 views

calculating max and joining tables

Just need a little help as new to SQL. I have a table which contains readingtime (records every 4 seconds during the day) and an energy reading. I wanted to know how much energy was used in a day. ...
2
votes
2answers
34 views

Compare multiple field to single IN query

I have a situation where I need to compare 3 different fields of a same table to a single IN query, eg: select unit from trips where owner1,owner2,owner3 in (select id from login) Can this be done ...
2
votes
2answers
218 views

“All joins can be rewritten to sub queries”, is this statement true?

I wonder if there any case that a join can not be converted to a sub-query. BTW: I am not discussing advantages and disadvantage of using sub-query or using join.
1
vote
1answer
311 views

CROSS APPLY versus INNER JOIN

I was reading a lot about CROSS APPLY versus INNER JOIN recently. I tried writing some queries and they both work in a similar fashion, the execution plan seems to be same as well. Maybe someone ...
-1
votes
1answer
56 views

View vs TableValued Function vs SubQuery

I have a scenario where i need to join with 3 tables to get the value for one field for second field join with 4 tables for third field join with 3 tables all above joins are inner and if data ...
1
vote
4answers
42 views

where clause: if this value from col x make sure col y is this value

I feel like this is an easy one for you Experts! I haven't done any kind of Advanced SQL in years. Fundamental question: select * from myTable where ((if col1 = 'this', make sure col2 = ...
0
votes
1answer
24 views

Count(*) as rolecount, filter by rolecount = 1

How can i filter out the rows who only got rolecount = 1? WHERE rolecount = 1 gives an error. Can't really figure out how to do it. SELECT u.uid AS uid, u.name AS name, u.picture AS picture, ...
0
votes
2answers
19 views

Is my subquery inneficient. It is extremely slow

I have two tables. Member information and login entries. I am simply trying to display the member information table sorted by the last login access date. So i created this query, but it is extremely ...
0
votes
1answer
50 views

If a query contains two identical subqueries, will they both run?

Say I have a SELECT query that contains two identical subqueries, like the below: SELECT (SELECT SUM(Price) FROM Product p WHERE p.GroupID = g.GroupID) AS 'TotalCost', (SELECT SUM(Price) ...
1
vote
1answer
82 views

Selecting data from multiple rows and insert it into a column

My table is like this: temp1 name city phone_no pincode ----------------------------------- amit Delhi 12345 123 Rajiv Bombay 836536 432 How do I transform the last ...
0
votes
1answer
120 views

How to INNER JOIN and OUTER JOIN within the same table?

I am using PostgreSQL and I have the following tables: Product ID | Name | Price Receipts ID | Month | Quantity | Product_ID (FK) Sales ID | Month | Quantity | Product_ID (FK) I am trying to ...
7
votes
3answers
306 views

Optimize select on subquery with COALESCE(…)

I have a large view that I use from within an application. I think I've narrowed down my performance problem, but I'm unsure how to fix it. A simplified version of the view looks like this: SELECT ...
0
votes
1answer
65 views

How do I count and order by unique entries?

I have this: SELECT tblClient.[Printed Name], tblClient.[Address 1], tblClient.[Address 2], tblClient.City, tblClient.State, tblClient.Zip, tblClient.Phone, tblClient.[Email Address] FROM tblClient ...
0
votes
0answers
64 views

How to include a GROUP_CONCAT list to result from subquery

I am trying to construct a nested query over the following 3 tables users, cars and bookings. Table users Table cars Table bookings +---------+ +--------+------------+ ...
-3
votes
2answers
169 views

how to solve slow running sub query TSQL

I have a problem with a slow running query. I have analysed the query with the sql server profiler but I can't seem to find a good solution. I did find why it is running slow. If ask the full ...
0
votes
0answers
822 views

Multiple SELECT subqueries in an INSERT statement in a stored procedure

So I had the following (genericized), which works just fine for me (barring errors in making the example generic) : DELIMITER // CREATE PROCEDURE stored_proc(IN val1 INT, IN date1 DATETIME, IN date2 ...
0
votes
1answer
51 views

How to optimise very simple query with subquery that only needs to execute once

SELECT products_id, products_model, products_iban_no, products_allocation_number FROM products WHERE products_model IN (SELECT products_model FROM products GROUP BY products_model HAVING ...
-1
votes
1answer
99 views

postgres: how to use subquery correctly?

I have the following query CREATE TABLE test_buffslip AS SELECT ST_INTERSECTS( (SELECT ST_Buffer(geom, 0.005) FROM segments where class = 7) ,geom) FROM segments; resulting in ...
0
votes
2answers
62 views

Problem in Nested Query

I have a query like this SELECT m.title FROM Movie AS M WHERE (SELECT MAX(r.stars) FROM rating AS r WHERE r.mID = M.mID); I want to show the MAX(r.stars) in my result set, what am I doing wrong?
0
votes
1answer
94 views

Very slow update with subquery (different tables)

I run this query to update "domains" table based on "log" table: UPDATE domains SET domains.mx_collected = 0 WHERE domains.domain IN (SELECT log.mail as domain_name FROM log WHERE `time` >= ...
2
votes
1answer
2k views

Writing a query to find all employee salaries that are higher than their managers

I have two tables with some dummy data. I need to find all employees that make more than their managers. I was led to believe the point of the exercise is to learn how to do subqueries. I can isolate ...
1
vote
1answer
146 views

Why does IN (subquery) perform bad when = (subquery) is blazing fast?

Have a look at these two queries: Simple: mysql> SELECT * FROM omgbatch JOIN omgoutput ON (idomgbatch=omgbatch) WHERE idomgoutput = (SELECT DISTINCT lastoutput FROM omgenvelope WHERE ...
0
votes
0answers
68 views

Latest of group in subquery is bottleneck, how to optimizing query?

See Original question I'm fetching the activity data for the latest activity for each company as part of a bigger query. I've been using the method provided by ypercube, and it works fine, but I find ...
1
vote
1answer
309 views

Select query for returning different data based on creation time

I have a view, which will Select data from table reports the select will filter the data based on multiple dates. reports table: id, key_id, position, created_at My select: pr.key_id, ...
0
votes
1answer
133 views

Take query results and insert into new table [closed]

So I have the following query: DECLARE @tbl TABLE(value int) *--The same function but with the number of the random numbers ;WITH Nbrs (n) AS ( SELECT 1 UNION ALL SELECT 1 + n FROM ...
0
votes
0answers
84 views

Unable to send WHERE parameters to subquery

I have the following problem (in PostgreSQL in combination with PostGIS): I have the following nice query who clusters multiple records. Although it uses PostGIS function, my problem is not PostGIS ...
1
vote
1answer
108 views

Aliases with correlated subqueries

I am looking at an example of correlated subquery with aliases from a PostgreSQL book: bpsimple=# SELECT * FROM orderinfo o, bpsimple=# (SELECT * FROM customer c WHERE town = 'Bingham') c bpsimple=# ...
2
votes
1answer
241 views

Find the second highest value from a subquery or derived table

I have a subquery which returns the following results. 'c003', '120000.00' 'c002', '90000.00' 'c001', '20000.00' 'c005', '8000.00' 'c004', '5000.00' I want to find the second highest value from ...
0
votes
2answers
793 views

How to find the same salaries and department names?

Write a query to display last name ,department number,and salary of any employee whose department number and salary both match the department number and salary of any employee using the following ...
0
votes
1answer
68 views

How do I solve this problem of a correlated subquery

We have an employee table with the following attributes : employee_id first_name last_name email phone_number hire_date job_id salary commission_pct manager_id department_id Display the manager ...
4
votes
1answer
52 views

Changes access method for non-correlated subquery

Oracle 11g R2 Unfortunately our application has per row security "features". We have a query that looks about like this: Bad, slow: SELECT someRow, someOtherRow FROM bigTableA a WHERE EXISTS ( ...
1
vote
2answers
152 views

optimization of expensive subquery

The following query gets the last report (where latitude and longitude and secs are <> 0) associated with the specified units: SELECT reports.* FROM reports INNER JOIN ...
2
votes
1answer
47 views

Update only the oldest entry in a table with foreign key criteria

I'm working on a forum website. What I am trying to do is updating the thread Title and Content (first post), which resides in different tables - "Threads" and "Posts". "Threads" "Posts" I'm ...
0
votes
0answers
35 views

subqueries with select

This below query works as expected: SELECT reports.* FROM reports INNER JOIN units ON units.id = reports.unit_id WHERE reports.unit_id IN (1111, 1112, 1113) ...