subquery

MySQL Date Between Subquery Question

Ok, I have a subquery that I am trying to work with and was needing a complicated question answered. There is a SQL tool that is being used to generate reports. With this tool, you can select dates as long as the date is in the outer select statement. So, you can use this tool to select a date range for instance from a simple select stat...

Providing additional data when selecting distinct rows

I have a table of login events coming from Active Directory. One type of these events are machine logins, which include the IP address of the machine doing the login. This is handy, since it provides a timestamped way to determine what machine was on what IP at a given time. I'm trying to construct a query that'll give me a timestamped l...

NHibernate: Conditionally load a calculated column

I have the following proprty <property name="Allocated" type="decimal" formula="(select sum(a.AllocationAmount) from Allocation a where a.TransactionId = TransactionId)" /> This loads the amount of a Transaction that has been allocated to invoices which is working beautifully. However, is most cases I don't care about this amount. ...

Error: SubQuery Returned More than One Value

I've got the following piece of SQL Code that is giving me a titular error. WHERE (SELECT Tokens FROM StringSplitter(@DocumentValue, '|', 1)) IN (SELECT Tokens FROM StringSplitter(@sortValue, '|', 1)) Where @DocumentValue and @sortValue are both concatenated strings separated by a delimiter (in this case, a '|'). The...

DataGridView cell search using a LINQ query

Howdy, all! I'm still relatively new to LINQ, and have done more stumbling around than anything, but I have really liked what I have seen so far. So with that in mind, I have a VB.NET search routine, part of which is provided, below, that checks all Text cells in a DataGridView for a given string (inclusive), using a basic set of neste...

Entity Framework LINQ - Subquery with Group By

Hi guys, I am trying to achieve a query which includes a subquery which itself includes grouping. I based my code from answers to this question The purpose of the code is to perform a simple de-duplication of the 'person' table based on the email address and return the latest person row. var innerQuery = (from p in db.Person ...

How to limit results of a LEFT JOIN

Take the case of two tables: tbl_product and tbl_transaction. tbl_product lists product details including names and ids while tbl_transaction lists transactions involving the products and includes dates, product-ids, customers etc. I need to display a web-page showing 10 products and for each product, the last 5 transactions. So far, n...

Problem with writing an SQL Sub Query

I know I must be missing something simple here...but I'm having problems with writing an SQL sub query. Given the following data user_id question_id answer_text 89 1 value1 89 2 value2 80 2 value2 99 2 value2 96 1 value1 96 2 ...

MySQL: reference subquery variable in another subquery

I'm working on a messaging application and am trying to find the number of new messages and the date of the most recent messages for all users with whom the current user has active conversations. The following query works, but expanding on it would mean I'd have to run the aggregate subquery twice to get both the count and last sent tim...

Select from 4 tables with joins and IN

I'm have 5 tables. First products like: id | country_ids | category_ids | users_ids 1 | 1,4,6 | 4,5,6,70 | 5,6,9 2 | 5,6,3 | 4,8,2,11 | 1,5,8 Second countries like: c_id | c_name 1 | Åland Islands 2 | Antarctica ... Third categories like: cat_id | cat_name 2 | Small 4 | Large ....

mySql sum a column and return only entries with and entry in last 10 minutes

heres a table, the time when the query runs i.e now is 2010-07-30 22:41:14 number | person | timestamp 45 mike 2008-02-15 15:31:14 56 mike 2008-02-15 15:30:56 67 mike 2008-02-17 13:31:14 34 mike 2010-07-30 22:31:14 56 bob 2009-07-30 22:37:14 67 bob 2009-07-30 22:37:14 22 ...

"Unknown column" because of subquery-in-subquery.

I need to do subquery in subquery what causes "Unknown column 't1.product_id' in 'where clause'". It's on line 7. in my example. How to solve this problem? SELECT *,product_id id, (SELECT GROUP_CONCAT (value ORDER By `order` ASC SEPARATOR ', ') FROM ( SELECT `order`,value FROM slud_data LEFT JOIN slud_...

mysql, use two select

hi, why i get error: Subquery returns more than 1 row SELECT name, cat_id, ( SELECT topic FROM category WHERE cat = u.cat_id ) AS topics FROM name u Thanks ...

mysql subquery in single table

Hi, I have 2 columns in an "entries" table: a non-unique id, and a date stamp (YYYY-MM-DD). I want to select "all of the entry id's that were inserted today that have never been entered before." I've been trying to use a subquery, but I don't think I'm using it right since they're both performed on the same table. Could someone help me ...

CakePHP sub query SQL in HABTM relation

I want to suggest related products by tags and sort order by the most matched. the HABTM model association between Product and Tag class Product extends AppModel { //.. var $hasAndBelongsToMany = array("Tag"); //.. } and vice versa in Tag model. also join-table name is "products_tags". for Ex.sample.. //just sample of Product conta...

django subquery via orm

I have models: class Site(models.Model): profile = models.ForeignKey(User) class Profile(models.Model): blacklist = models.ManyToManyField(Site) How can i do equivalent of this query via django orm? SELECT * FROM site WHERE 2 NOT IN (SELECT site_id FROM profile_blacklist WHERE profile_site.profile_id=site.profile_id) I nee...

SQL query with complex subquery

I have two tables, Foo and Bar. Foo contains a foreign key to Bar's primary key (bar_id). Bar is structured to allow a parent/child relationship to itself through a foreign key (bar_parent_id) to another record in Bar. This relationship is limited such that any Bar record that has a parent cannot itself be a parent. However, any given pa...

Linq to Entities 4.0 - Optimized Query and multiple calls to database in a single query

Hi, Can anyone tell me if the following query calls the database multiple times or just once? var bizItems = new { items = ( from bl in db.rc_BusinessLocations orderby bl.rc_BusinessProfiles.BusinessName select new BusinessLocationItem { BusinessLocation = bl, BusinessProfile ...

MySQL Query, Subquery optimization, SELECT, JOIN

I have one table with some data and I want select newest data for each type... Table: +----+------+------+---------------------+ | ID | data | type | date | +----+------+------+---------------------+ | 1 | just | 2 | 2010-08-07 14:24:48 | | 2 | some | 2 | 2010-08-07 18:07:32 | | 3 | data | 9 | 2010-08-06 02:5...

SQL Server DELETE is slower with indexes

I have an SQL Server 2005 database, and I tried putting indexes on the appropriate fields in order to speed up the DELETE of records from a table with millions of rows (big_table has only 3 columns), but now the DELETE execution time is even longer! (1 hour versus 13 min for example) I have a relationship between to tables, and the col...