Hi folks, i'm trying to do a spatial query in sql 2008 -> for a given list of POI's (point of interest, long/lat GEOGRAPHY data), which postcodes do they exist in (multipolygon GEOGRAPHY data). So this is the query i tried, but it's syntactically incorrect:- SELECT PostCodeId, ShapeFile FROM Postcodes a WHERE a.ShapeFile.STIntersects(...
I have two tables named foo and bar, hypothetically speaking. foo has columns foo_id, foo_fluff bar has columns bar_id, foo_id, timestamp I need a query which will retrieve return one row for any foo_id that the table bar contains, with the latest timestamp. So, if bar has three rows, two of which have a foo_id of 1, and 1 of which ha...
I'll try to explain this better on another question. This is the query that I think should work but, off course, MySQL doesn't support this specific subselect query: select * from articles a where a.article_id in (select f.article_id from articles f where f.category_id = a.category_id order by f.is_stic...
I would like to provide a WHERE condition on an inner query by specifying innertable.id = outertable.id. However, MySQL (5.0.45) reports "Unknown column 'outertable.id' in 'where clause'". Is this type of query possible? The inner query is pivoting rows to columns using a GROUP BY. This could be entirely be performed in the outer query,...
I'm having difficulty translating sql to linq syntax. I have 2 tables (Category and CategoryListing) which reference each other with CategoryID. I need to get a list of all the CategoryID in Category Table and the Count of CategoryID for all corresponding matches in the CategoryListing table. If a CategoryID is not present in Category...
Hi, I have this query: SELECT p.id, r.status, r.title FROM page AS p INNER JOIN page_revision as r ON r.pageId = p.id AND ( r.id = (SELECT MAX(r2.id) from page_revision as r2 WHERE r2.pageId = r.pageId AND r2.status = 'active') OR r.id = (SELECT MAX(r2.id) from page_revision as r2 WHERE r2.pageId = r.pageId) ) Which...
I'm using a query which generally executes in under a second, but sometimes takes between 10-40 seconds to finish. I'm actually not totally clear on how the subquery works, I just know that it works, in that it gives me 15 rows for each faverprofileid. I'm logging slow queries and it's telling me 5823244 rows were examined, which is odd...
I have two tables with a 1:n relationship: "content" and "versioned-content-data" (for example, an article entity and all the versions created of that article). I would like to create a view that displays the top version of each "content". Currently I use this query (with a simple subquery): SELECT t1.id, t1.title, t1.con...
I just tried to do a correlated subquery in the FROM clause of a SELECT statement in Oracle, but I was given an error indicating that I couldn't do the correlation (something to the effect that Obs.pID was not recognized). Should this work? FROM ml.Person Person JOIN ml.Obs ON Person.pID = Obs.pId JOIN (SELECT ObsMax2.pId, ObsMax2.h...
I need to do something like the following: select d.ID, count(from Lead l where l.DateCreated > DATEADD(day, -30, :todays_date) AND l.DealerId=d.ID), count(from Lead l where l.DateCreated < DATEADD(day, -30, :todays_date) and l.DateCreated >= DATEADD(day, -60, :todays_date)) FROM Dealer d GROUP BY d.ID Ordinarily I could do this: SE...
I need to insert a row into a table, with one field value being calculated from another table. Rather than doing two queries and risking a race condition, I thought it'd be better to do it all in one statement. INSERT INTO `myTable` (`someData`, `averageAtThisTime`) VALUES ( "some stuff", SELECT AVG(`myField`) FROM `myOtherTable...
Given these two tables: Foo (id, name) -- PK = id Bar (fooId, value) -- PK = composite, fooId + value -- value is a non-negative int How can I find all the Foo.names where there is no corresponding Bar,value above 0? eg: Foo id name 1 a 2 b 3 c Bar fooid value 1 0 1 1 2 ...
Lets say I have the following tables: Customers Products CustomerProducts Is there a way I can do a select from the Customers and Products tables, where the values are NOT in the map table? Basically I need a matched list of Customers and Products they do NOT own. Another twist: I need to pair one customer per product. So If 5 cus...
I have a very complex MySQL query that includes use of the same subquery three times. Will MySQL actually run the subquery three times? (It's an expensive one.) If so, is there a way for me to tell MySQL to save or cache the results so it won't do that? I could save the data in a large array then re-feed it to MySQL, but I'd rather not m...
Hi, I have two tables (Management and Employee). The management table tracks the different management teams that have managed company X in past few years. Each management team is given an ID (i.e: managementnr), and each team has a CEO (namely ceoname). The employee table tracks employees working for company X (basically just their ...
I have something similar to the following: SELECT c.id FROM contact AS c WHERE c.id IN (SELECT s.contact_id FROM sub_table AS s LEFT JOIN contact_sub AS c2 ON (s.id = c2.sub_field) WHERE c2.phone LIKE '535%') ORDER BY c.name The problem is that the query takes a very very very long time (>2minutes), but if I take the ...
SELECT number, count(id) FROM tracking WHERE id IN (SELECT max(id) FROM tracking WHERE splitnr = 'a11' AND number >0 AND timestamp >= '2009-04-08 00:00:00' AND timestamp <= '2009-04-08 12:55:57' GROUP BY ident) GROUP BY number ...
Hi, I am trying to optimize my MySQL queries and I need some help. Here is my current query : SELECT *, (SELECT name FROM stores WHERE id = products.store_id) AS store_name, (SELECT username FROM stores WHERE id = products.store_id) AS store_username, (SELECT region_id FROM stores WHERE id = products.store_id) AS re...
I have the following SQL statement in a trigger that fires on deletion: UPDATE bk2_InfoPages SET SortOrder = SortOrder - (SELECT COUNT(*) FROM Deleted d WHERE d.SortOrder <= SortOrder) My problem is that the very last SortOrder refers to the Deleted table and not to the bk2_InfoPages table. I am not allowed to add an alias to the bk2_...