subquery

VERY WEIRD T-SQL issue - query works but not if I encapsulate it as a sub-query to just select * from ()

Hello All, I have the following code which works FANTASTIC. Except - It is a sub-query to another (final) outer query and when I attempt to encapsulate this as a sub-query it refuses to run. To make it simple, I just did a "SELECT * FROM ( MY QUERY HERE)" and that even fails to run. I can only figure I am putting the right ")" in the...

Why is LINQ to Entities making a subquery for me?

I'm using .NET 4 and the Entity Framework to construct a simple query. Here's the C# code: return Context.Files.Where(f => f.FileHash != 40) .OrderByDescending(f => f.Created) .Take(5); When I trace the query using ObjectQuery.ToTraceString(), I find the following subquery: SELECT TOP (5) [P...

LINQ and how to return a list of a specific type

Hi, I have 2 tables (Document and DocumentClass) that have the following columns: DocumentClass: DocClassID, Name, ParentID Document: DocID, Name, DocClassID The DocumentClass table contains parent and child records and the relationship between a parent and a child is the ParentID column. A Document record is linked with a child rec...

MySQl : How do I use Count on a Sub-Query?

I've got a MySQL statement that selects a name and also makes a ranking. SELECT t.name, (SELECT COUNT(*) FROM my_table1 z WHERE z.type LIKE '%Blue%' AND t.type LIKE '%Blue%' AND (z.score1+ z.score2 + z.score3 + z.score4) >= (t.score1+ t.score2 + t.sco...

MySql - Inserting multiple rows with a joined subquery?

This query will return a list of project IDs that represent forum threads: SELECT id FROM `proj_objects` WHERE type='fthread'; This query will subscribe a user (whose ID in the users table is '37') to the forum thread with an ID of '122': INSERT INTO `subscrips` VALUES ( 37, 122 ) ; I'd like to insert multiple rows that will subsc...

Can't get head round mysql subquery

Im having trouble getting my head round subqueries in Mysql. Fairly simple ones are ok, and most tutorials I find rarely go beyond the typical: SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2); What I am trying to pull out of my database is the following (I'll try my best to explain this without any background on our db): Re...

MySQL: Sum values in subqueries

I have information on school athletics, with tables for school, season, cashflow, and cashflow_group. I'm trying to query for all schools with cashflow in one or more given cashflow_groups within a user-specified range. I need to query multiple different categories in the same query. I'm having trouble. My query is below. The reason I d...

Subquery or a double query?

Hi all, For a client I have built a travelagency website. Now they asked me to optimize and automize some things. One of these things is that the accommodations shown in the searchlist must be bookable. Simplified this is the structure I have an AccommodationObject, an Accommodation has 1 or more PricePeriods. What I need to do pick ...

Syntax for SQL Not In List?

I am trying to develop a T-SQL query to exclude all rows from another table "B". This other table "B" has 3 columns comprising its PK for a total of 136 rows. So I want to select all columns from table "A" minus those from table "B". How do I do this? I don't think this query is correct because I am still getting a duplicate record e...

Question on multi-row insertion with subqueries

Say I have the following 2 tables, CREATE TABLE t1( name VARCHAR(25) NOT NULL, time INT, a INT ); CREATE TABLE t2( name VARCHAR(25) NOT NULL, time INT, b INT ); and Im looking to pull all the values (a) out of t1 with a given time, all the values with the previous time (say just time-1 for convenience) then for each name ...

How to optimize this low-performance MySQL query?

Hi there, I’m currently using the following query for jsPerf. In the likely case you don’t know jsPerf — there are two tables: pages containing the test cases / revisions, and tests containing the code snippets for the tests inside the test cases. There are currently 937 records in pages and 3817 records in tests. As you can see, it t...

Linq Subquery Where Clause

I need some help with thsi linq query. It shoudl be fairly simple, but it is kicking my butt. I need to use a subquery to filter out data from the main query, but every path I have tried to use results in failure. The subquery by itself looks like this. int pk = (from c in context.PtApprovedCertifications where c.FkosParticip...

Why is this (non-correlated) subquery causing such problems?

I've got a large query where a simple subquery optimization dropped it from 8 minutes down to 20 seconds. I'm not sure I understand why the optimization had such a drastic effect. In essence, here's the problem part: SELECT (bunch of stuff) FROM a LEFT OUTER JOIN b ON a.ID = b.a LEFT OUTER JOIN c ON b.ID = c.b ... ... I...

Calculations on Subquery in strong typed dataset

Hello, following background: i have a Table-valued Function that returns a Table that is a kind of filtered view(views can not be parameterised) on a large table. Now i need many row count values on different conditions. Is it possible to get all count values in one query without having to query this function for every condition? For th...

Selecting table using case clause in from clause

I have 8 table that contain different specific value for computer peripheral they are glpi_device_ram, glpi_device_hdd, glpi_device_gfxcard, glpi_device_sndcard. Each table has the same designation column in each table that contain device name. i have table glpi_computer_device that contain FK_device that contain id for each 8 table abov...

SQL: "NOT IN" subquery optimization or alternatives

I have two database tables: "places" and "translations". The translations of places names are made by selecting records from "places", which don't have the translations to the specified language yet: SELECT `id`, `name` FROM `places` WHERE `id` NOT IN (SELECT `place_id` FROM `translations` WHERE `lang` = 'en') This worked fine with 7 ...

How do I limit the result of a subquery in MySQL?

Is there a way of limiting the result of a subquery? The sort of thing I'm trying to achieve can be explained by the query below: SELECT * FROM product p JOIN ( SELECT price FROM supplierPrices sp ORDER BY price ASC LIMIT 1 ) ON (p.product_id = sp.product_id) The idea would be to get only the lowest price for a particu...

Sub-query in tuple constraint DB2

Hi, In my database course we use a book (Database System - the Complete Book) which says the following is a valid create table statement in standard SQL: CREATE TABLE Participants ( meetid INT NOT NULL, -- ... CONSTRAINT RoomConstraint CHECK (1 >= ALL (SELECT num FROM Numbers) ); But DB2 complains and gives 20 pos...

Getting more details out of a SQL subquery

I have a SQL Query giving me a list of double records in my database. select periodid, itemid from periodscore group by periodid, itemid having count(*) > 1 This works as expected, but now I would like to retrieve additional fields of these records (such as date last updated etc). So I tried: select * from periodscore where period...

Combining two select queries

A the moment I am using two queries one is called initially, and the second is called during a loop through the results of the first. I want to combine both queries, but have been unable to so far. The tables the queries are pulling from are: +--------------+ +--------------+ +--------------------------+ | table_1 | | t...