subquery

Impact of ordering of correlated subqueries within a projection

I'm noticing something a bit unexpected with how SQL Server (SQL Server 2008 in this case) treats correlated subqueries within a select statement. My assumption was that a query plan should not be affected by the mere order in which subqueries (or columns, for that matter) are written within the projection clause of the select statement...

T-sql Common expression query as subquery

I have the following query: WITH Orders(Id) AS ( SELECT DISTINCT anfrageid FROM MPHotlineAnfrageAnhang ) SELECT Id, ( SELECT CONVERT(VARCHAR(255),anfragetext) + ' | ' FROM MPHotlineAnfrageAnhang WHERE anfrageid = Id ORDER BY anfrageid, erstelltam FOR XML PATH('') ) AS Descriptions FROM Orders Its concatenates varchar values of dif...

LEFT OUTER JOIN in NHibernate with SQL semantics

Hi, Is it possible to use HQL/ICritera to produce a query with the same semantics as the following SQL query: select table1.A, table2.B, count(*) from table1 left join (select table2.parent_id, table2.B from table2 where table2.C = 'Some value') as table2 on table2.parent_id = table1.id group by table1.A, table2.B order by table1.A ...

SQL Oracle LEFT JOIN and SUBQUERY error: ORA-00905: missing keyword

Hello everyone. Asking for your help on this Oracle query. It's giving me the error 2 "ORA-00905: missing keyword". It was working fine before I added the LEFT JOIN statement. Obviously it won't deliver the information as we need it without the LEFT JOIN statement. Please provide any help to know which keyword is missing in this query ...

SQL: Join vs. subquery

I am an old-school MySQL user and always preferred JOIN over sub-query. But nowadays everyone uses sub-query and I hate it, dunno why. Though I've lack of theoretical knowledge to judge myself if there are any difference. Well, I am curious if sub-query as good as join and there is no thing to worry about? ...

Nested query to find details in table B for maximum value in table A

I've got a huge bunch of flights travelling between airports. Each airport has an ID and (x,y) coordinates. For a given list of flights, I want to find the northernmost (highest x) airport visited. Here's the query I'm currently using: SELECT name,iata,icao,apid,x,y FROM airports WHERE y=(SELECT MAX(y) FROM ...

subqueries in UPDATE SET (sql server 2005)

I have a question about using subqueries in an Update statement. My example: UPDATE TRIPS SET locations = city + ', ' FROM (select Distinct city from poi where poi.trip_guid = trips.guid) Is it possible to refer to main table value (trips.guid) in sub...

SQL Server 2005: Subquery in EXEC ?

EXEC [dbo].[pr_cfgAddFact] @SettingName = 'TransferBatch', @RoleFK = SELECT TOP 1 rolepk FROM cfgRole WHERE cfgRole.Name = 'SuperAdmin' Why would SQL complain about the SELECT clause here? I am trying to run the proc with the sub query getting the data. ...

Updating rows using "in" operator in "where" clause

Hi. I stumbled upon SQL behavior I don't understand. I needed to update several rows in a table at once; started with just finding them: SELECT * FROM some_table WHERE field1 IN (SELECT ...) This returned a selection of about 60 rows. Now I was pretty confident I got the subquery right, so I modified the first part only: UPDATE so...

SQL MIN in Sub query causes huge delay

I have a SQL query that I'm trying to debug. It works fine for small sets of data, but in large sets of data, this particular part of it causes it to take 45-50 seconds instead of being sub second in speed. This subquery is one of the select items in a larger query. I'm basically trying to figure out when the earliest work date is that ...

correlated query /subquery VS join query

can we always convert a usual subquery/correlated subquery to join type query? ...

Using an interface for a subquery in NHibernate

I normally query interfaces using DetachedCriteria in NHibernate: DetachedCriteria crit = DetachedCriteria.For<IParent>(); And this works fine. I now want to create a subquery for a child object thus: DetachedCriteria subcrit = DetachedCriteria.For<IChild>(); and add it to the criteria like this (kind of, p.Child is actually an al...

SQL Server: join on derived table that contains WITH clause?

I'd like to join on a subquery / derived table that contains a WITH clause (the WITH clause is necessary to filter on ROW_NUMBER() = 1). In Teradata something similar would work fine, but Teradata uses QUALIFY ROW_NUMBER() = 1 instead of a WITH clause. Here is my attempt at this join: -- want to join row with max StartDate on JobModel...

Is possible to reuse subqueries?

Hello, I'm having some problems trying to perform a query. I have two tables, one with elements information, and another one with records related with the elements of the first table. The idea is to get in the same row the element information plus several records information. Structure could be explain like this: table [ id, name ] [...

How to optimise MySQL query containing a subquery?

I have two tables, House and Person. For any row in House, there can be 0, 1 or many corresponding rows in Person. But, of those people, a maximum of one will have a status of "ACTIVE", the others will all have a status of "CANCELLED". e.g. SELECT * FROM House LEFT JOIN Person ON House.ID = Person.HouseID House.ID | Person.ID | Person...

View and order by

Why can't a subquery of a view have an order by clause?Similarly why one cant change/delete a row through a view when 1)group by is used in view 2)distnct is used in view ...

In SQL, why is "Distinct" not used in a subquery, when looking for some items "not showing up" in the other table?

Usually when looking for some items not showing up in the other table, we can use: select * from gifts where giftID not in (select giftID from sentgifts); or select * from gifts where giftID not in (select distinct giftID from sentgifts); the second line is with "distinct" added, so that the resulting table is smaller, and probably...

In SQL, can we always write an inner join statement as a main query and subquery?

In SQL, can we always write an inner join statement as a main query and subquery? For example, select * from gifts g where g.giftID in (select giftID from sentGifts); can do a join and show the gifts sent in the sentGifts table, but it won't be able to show the sentTime because that is inside the subquery? ...

In SQL, can we always write an inner join statement as a main query and subquery if we only want to find the intersection?

In SQL, can we always write an inner join statement as a main query and subquery or vice versa if we only want to find the intersection? For example, select * from gifts g where g.giftID in (select giftID from sentGifts); can do a join and show the gifts sent in the sentGifts table, but it won't be able to show the sentTime because ...

How can I combine result and subquery for IN comparison (mysql)

In order for a school project i need to create the following situation within one mysql query. The situation is as such, that a child's tags and a parent's tags need to be combined into one, and compared to a site's tags, depending on a few extra simple equals to lines. For this to happen I only see the option that the result of a subq...