subquery

Rewriting correlated subquery as JOIN?

UPDATE: Thanks to Sifu Bill's advice I have amended the SQL query. Now it returns the correct number of distinct assets (five). Is it possible to rewrite the following correlated subquery as a JOIN? SELECT TOP 100 PERCENT Asset_ID, work_order_id, status_id, downtime_hours, date_completed FROM dbo.mtvw_wo_reason1 WHERE (Asset_ID IN (SEL...

Using SELECT TOP from one column, then sorting on a different column

I'm using SQL Server 2005, and I want to query for the vendors generating the most revenue, sorted by the vendor's name. Below is the query I have tried. The inner subquery gets the 15 largest vendors sorted by revenue, and I try to order those results by the vendor name. SELECT Revenue, VendorName FROM ( SELECT TOP 15 SUM(...

Zend_Db_Table subquery

Hi, I have a some SQL that I want to use with ZendFW, but I can't get it working and it's driving me crazy. I get the correct result with this query: SELECT DISTINCT e.festival_id FROM entries AS e, mail_log as m WHERE e.status = 1 AND e.festival_id NOT IN (SELECT m.entry_id FROM entries AS e, mail_log as m WHERE m.entry_id = e.festi...

SQL Server - Selecting records with specific, multiple entries in an associative table

I’m working in SQL Server with the following sample problem. Brandon prefers PC’s and Macs, Sue prefers PC’s only, and Alan Prefers Macs. The data would be represented something like this. I've had to make some compromises here but hopefully you get the picture: TABLE 1: User uID (INT PK), uName (VARCHAR) 1 'Brandon' 2 ...

MySQL: Returning multiple columns from an in-line subquery

I'm creating an SQL statement that will return a month by month summary on sales. The summary will list some simple columns for the date, total number of sales and the total value of sales. However, in addition to these columns, i'd like to include 3 more that will list the months best customer by amount spent. For these columns, I nee...

Can i force mysql to perform subquery first?

I have query like this: SELECT `table_1`.* from `table_1` INNER JOIN `table_2` [...] INNER JOIN `table_3` [...] WHERE `table_1`.`id` IN( SELECT `id` FROM [...] ) AND [more conditions] When I use EXPLAIN, there is 'DEPENDENT SUBQUERY' at the end, but I want this subquery to be performed first, before other conditions. Is is poss...

SQL to Linq translation with inner join and 2 left joins (with sub-query)

Hi there! I have the following query which works fine in SSMS. Im using LinqPad (C#) but really puzzling to succeed with the left outer join in LinqToSql: SELECT DISTINCT A.LocID, V1.PrfValue AS pID, V2.PrfValue AS sID, D.DivisionManager, A.IsApproved, A.DateCreated FROM dbo.Locations AS A INNER JOIN dbo.D...

Procedurally transform subquery into join

Is there a generalized procedure or algorithm for transforming a SQL subquery into a join, or vice versa? That is, is there a set of typographic operations that can be applied to a syntactically correct SQL query statement containing a subquery that results in a functionally equivalent statement without a subquery? If so, what are they (...

SQL NOT IN Query

Can anyone help me with this MySQL query? SELECT p.ProductID, p.StoreID, p.DiscountPercentage FROM Products p WHERE p.IsSpecial = 1 AND p.SpecialDate >= date_sub(now(),interval 15 minute) AND p.DiscountPercentage >= ?DiscountPercentage AND p.ProductID NOT IN (SELECT lf.LiveFeedID ...

Using LINQ to query/subquery multiple methods (Quartz.NET example for groups, jobs, and triggers)

Hello all, Definitely a LINQ newbie, but very experienced with SQL and C# and wondering if this is possible in LINQ. If so, I could use it other places, but I figured this would be a good starting point (and help to simplify/clean up some code). This could be more generalized, but I figured this might be a good real-life example that co...

Using EXISTS with MySQL

I have this simple query that works on all other database systems, but fails with MySQL: UPDATE points p SET p.userid = 5224 WHERE p.userid = 2532 AND NOT EXISTS ( SELECT 1 FROM points q WHERE q.userid = 5224 AND q.game = p.game ) I get the following error message: #1093 - You can't specify target table 'p' for up...

NHibernate criteria to query parent objects with at least one child object?

In my project, Lines can be grouped and a Group has a type which can be either Crossing (1) or Parallel (2). I need to find all lines which has at least one group of a specified type (in this case, 1). The Id of a given line can be either on column LineA or LineB of a group. Here is where i got so far: Criteria crit = session.CreateCrit...

update via subquery, what if the subquery returns no rows?

I am using a subquery in an UPDATE: UPDATE tableA SET x,y,z = ( (SELECT x, y, z FROM tableB b WHERE tableA.id = b.id AND (tableA.x != b.x OR tableA.y != b.y OR tableA.z != b.z))) ); My question is, what happens if the subquery returns no rows?...

Subquery to String?

Hi all, I have a table containing countries: id country ------------ 0 Monaco 1 Mongolia 2 Montenegro 3 Morocco 4 Mozambique 5 Myanmar I have a sub query that looks like this. (SELECT country FROM COUNTRIES WHERE id < 10) AS ´Trip´ I want to have that subquery to be formatted as a string like this: '...

SQL-Query with a multi-part identifier problem within a subquery

Hi All, i've a query that is supposed to return the sum for "status"-duration entries. The duration is calculated by using datediff(n, datestamp, (subquery that returns the datestamp ending the current status, i.e. finds the next fitting "status change"-entry after the one locked at) My Problem is that the following query returns an mu...

NHibernate Joining on non-mapped property

Hi, I have two classes SystemInvitation and User. User has a property called Email and SystemInvitation has a property called InviteesEmailAddress. There is no relationship in the domain between these properties. Is it possible using the Criteria API to produce a query like: select si.InviteesEmailAddress , si.Identifier ...

SQL "In" Statement Match Anything

If I have a query like this SELECT * FROM table1 WHERE col1 IN ({SUBS}) Is there anything I can replace {SUBS} with that will return all rows in the table? Further details: I am building the SQL dynamically in my app, so I cannot (should not) edit other parts of the query except what's in braces. So, SELECT * FROM table1 will no...

mysql random rows

how to form a query to select 'm' rows randomly from a query result which has 'n' rows. for ex; 5 rows from a query result which has 50 rows i try like as follows but it errors select * from (select * from emp where alphabet='A' order by sal desc) order by rand() limit 5; u can wonder that why he needs sub query, i need 5 differen...

Nested SELECT clause in SQL Compact 3.5

In this post "select with nested select" I read that SQL Compact 3.5 (SP1) support nested SELECT clause. But my request not work: t1 - table 1 t2 - table 2 c1, c2 = columns select t1.c1, t1.c2, (select count(t2.c1) from t2 where t2.id = t1.id) as count_t from t1 Does SQL Compact 3.5 SP1 support nested SELECT clause in this c...

Cannot perform an aggregate function on a subquery

Can someone help me with this query? SELECT p.OwnerName, SUM(ru.MonthlyRent) AS PotentinalRent, SUM( (SELECT COUNT(t.ID) * ru.MonthlyRent FROM tblTenant t WHERE t.UnitID = ru.ID) ) AS ExpectedRent FROM tblRentalUnit ru LEFT JOIN tblProperty p ON p.ID = ru.PropertyID GROUP BY p.OwnerName I'm having problems with the se...