subquery

Edit query in MS-Access with a subquery containg a SUM() field

When I open this query in Access (which is just a collection of 2 linked tables) I'm able to edit the data with no problems whatsoever. SELECT O.*, PP.skuapexid FROM tblSkuBestellingen AS O INNER JOIN tblSkuApex AS PP ON (PP.begindatum <= O.besteldatum) AND (PP.sku = O.sku) WHERE NOT EXISTS ( SELECT * FROM tblSkuApex PP2 ...

Is order by clause allowed in a subquery

Is there any reason why or why not you should do an 'order by' in a subquery? ...

MySQL Subquery returning incorrect result?

I've got the following MySQL query / subquery: SELECT id, user_id, another_id, myvalue, created, modified, ( SELECT id FROM users_values AS ParentUsersValue WHERE ParentUsersValue.user_id = UsersValue.user_id AND ParentUsersValue.another_id = UsersValue.another_id AND ParentUsersValue.id < UsersValue.id ORDER ...

Using Subquery in Select statement.

I've got a query that outputs the following table: (0) Age <= 19----------76-------0.12 (1) Age 20 – 24--------661------1.06 (2) Age 25 – 29-------4060------6.53 (3) Age 30 – 34-------7231------11.64 (4) Age 35 – 39-------9281------14.94 (5) Age 40 – 44-------9539------15.35 Total ----------------30848 -----49.65 The first...

Why not "Invalid column name XYZ" error in subquery; although column name is not in subquery table?

When I run this query SELECT CustomerId FROM Stocks.dbo.Suppliers It gives me this error. Invalid column name 'CustomerId'. This error is valid as there is no column CustomerId in Suppliers table; but when I use same query in subquery it does not give any error E.g. SELECT * FROM SomeOtherDb.dbo.Customer WHERE CustomerId In( SEL...

How to get number of specific rows from a different table in a subquery

I know it's possible, but I'm not experienced enough to know how to do subqueries. Here's the situation: Table 1: +--------------------+--------------------+ | v_id | v_name | +--------------------+--------------------+ | 1 | v_name1 | +--------------------+--------------------+ | et...

How to limit an SQL result set to not too common items

Problem: I have a list of names and addresses. Some names (persons) have the same address (street, zip code, town) like others. I want to select all those names with addresses with no more than three occurrences and from the rest the first three names each of a bunch pointing to the same address. Example: Albert | Adr1 Berta | Adr1 Cesa...

How can I copy all fields of one table to another, wider table in SQL Server 2005 Express?

I know my title isn't exactly worded well, so let me clarify. I'm using SQL Server 2005 Express. I have a table that basically stores a "template," if you will. Using a car as an example, the fields would be something like: TemplateID Color Make Model Now, I have another table that represents an "instance" of the template. It cont...

Finding the SUM of values of a column of all records in sql 2005

I want to find the SUM of values in a column weight. I want this sum for all records that are identified with a common value in one of the columns name. Further, I want to consider only those records that have a certain value in the column type. name ...

Is there a better way to write this SQL than using WHERE ... IN (subquery)?

is there a better way to write this SQL than using WHERE ... IN (subquery)? SELECT device.mac, reseller.name, agent.name FROM device LEFT JOIN global_user ON device.global_user_id = global_user.id LEFT JOIN agent ON global_user.id = agent.global_user_id LEFT JOIN reseller ON global_user.id = reseller.global_user_id...

Sql Subquery with sum

I have two tables Invoices and Payments. invoices have payments. I want to write a query that displays unpaid invoices and the remaining amount of the invoice, which is calculated by summing up the payments of the invoice and subtracting it from the invoice amount. I tried this query but it doesn't work. please how can i do it. SELECT I...

mysql too long query

Hi, i have a problem with this mysql query. It take vmore as 1 day to execute ... The query is : INSERT INTO traduction (traduction.`id`,traduction.`traduction`,traduction.`language`,traduction.`type`) ( SELECT cities.id,cities.name, '', 'city' FROM cities WHERE cities.id NOT IN ( SELECT traduction.`id` FROM traduction ...

In which sequence are queries and sub-queries executed by the SQL engine?

Hello I made a SQL test and dubious/curious about one question: In which sequence are queries and sub-queries executed by the SQL engine? the answers was primary query -> sub query -> sub sub query and so on sub sub query -> sub query -> prime query the whole query is interpreted at one time There is no fixed sequence of interpretati...

Hibernate Criteria API and Scalar Subqueries

Hi, I want to translate a HQL query to Criteria API but I don't know if it's possible to write the same thing with Criteria. The HQL looks like this: select distinct new DataTransferObject(property1, property2, ..., (select NVL(property3, null) from Table1 where property3 in elements(...) and ... ), property4, ..., (select .....), ...)...

How can I SELECT the names of all groups a person belongs to using a JOIN instead of IN?

Consider the following simplified example: CREATE TABLE groups ( gid INTEGER PRIMARY KEY, name VARCHAR(100) ); CREATE TABLE people ( pid INTEGER PRIMARY KEY ); CREATE TABLE people_groups ( gid INTEGER NOT NULL CONSTRAINT fk_people_groups_group REFERENCES groups(gid), pid INTEGER NOT NULL CONSTRAINT fk_p...

MySQL left join subquery fail

Following query runs well in MySQL 5.x SELECT m_area.id, m_area.cn_areaName, m_area.de_areaName, m_area.en_areaName,m_area.jp_areaName,t_shop.count FROM m_area left join ( select t_shop.areaID, count(areaID) AS count from t_shop group by t_shop.areaID ) t_shop on m_area.id = t_shop.areaID However, when I have to run it in a 4...

How do you create an index on a subquery factored temporary table?

I've got a query which has a WITH statement for a subquery at the top, and I'm then running a couple of CONNECT BYs on the subquery. The subquery can contain tens of thousands of rows, and there's no limit to the depth of the CONNECT BY hierarchy. Currently, this query takes upwards of 30 seconds; is it possible to specify indexes to put...

Creating subquery using IN

I have a question on making a good subquery for this problem Let's say we have a table users, and links. users +++++++++ id name 1 name1 2 name2 3 name3 +++++++++ links +++++++++ id link 1 link1 2 link1 3 link1 +++++++++ And lets say i have a relationship table like this name_links ++++++++++++ uid lid 1 1,3 2 1,2,3...

mysql problem: populate table from another table but referencing ID instead of name

Hi, I'm now trying to populate my 'testMatch' table (below) with data from my 'summary' table: TESTMATCH TABLE +------------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------------+--------------+------+-----+---------+-------+ | match_id | int...

Optimizing Multilevel MySQL subqueries (folksonomy and taxonomy)

I was reading the great tagging article by Nitin Borwankar and he started me thinking of the ways to implement differnet levels of searches using two tables. tags { id, tag } post_tags { id user_id post_id tag_id } I started with the simple example of T(U(i)) which means all tags of all users that have an item i. I was ab...