subquery

Sqlite subqueries : in one big query or in a for loop ?

I was planning to benchmark that but since it's a lot of work, I'd like to check if I didn't miss any obvious answer before. I have a huge query that gets some more details for each row with a subquery. Each row is then used in a ListAdapter that is plugged in a ListView, so another loop take each row one by one to make it a ListItem. ...

SQL Server 2005 error when grouping using subquery.

Using SQL Server 2005 I'm trying to group based on a case statement with a subquery, but I'm getting an error ("Each GROUP BY expression must contain at least one column reference. "). I can work round it quite easily, but can anyone explain the error? I've got a column reference to #header.header. create table #header (header int) crea...

Help with Correlated Queries in SQL

Please help!! I am brand new to SQL programming and am teaching myself everything as I go along. I’ve finally run into a problem I can’t seem to tackle. Hopefully someone can help. I have the following tables. What I need to do is use the RECDATE in FROISEDI and count all the distinct dates (I4C7DZ) prior to and including that date ...

How can I concatinate a subquery result field into the parent query?

Hi folks, DB: Sql Server 2008. I have a really (fake) groovy query like this:- SELECT CarId, NumberPlate (SELECT Owner FROM Owners b WHERE b.CarId = a.CarId) AS Owners FROM Cars a ORDER BY NumberPlate And this is what I'm trying to get... => 1 ABC123 John, Jill, Jane => 2 XYZ123 Fred => 3 SOHOT J...

Need some help making an Indexed view with 2 COUNT_BIG's

Ok, I'm trying to make an indexed view that is against a simple table that stores the results of what people think is good/bad for a post. This is the results of a thumbs up / thumbs down, voting on posts. So here's my pseduo fake table :- HelpfulPostId INT IDENTITY(1,1) NOT NULL PRIMARY KEY, PostId INT NOT NULL, IsHelpful BIT NOT NULL...

SQLite query optimization (subquery and join) help

I have a table for a Statistical project. Structure is like this: CREATE TABLE NewStatHistory ( StatHistoryID uniqueidentifier PRIMARY KEY NOT NULL, DateEntered dateTime NOT NULL, DateApplies dateTime NOT NULL, WhoEnteredID uniqueIdentifier NOT NULL, PostingID uniqueIdentifier NULL, EnteredValue decimal(19,5) NO...

mysql - subqueries and joins

Hey, I'm not quite sure if this is the right approach, this is my situation: I'm currently trying to select 15 galleries and then left join it with the user table through the id but I also want to select one random picture from each gallery however from what I know you can't limit the left join (picture) to only pick up one random pict...

How to properly construct SQL subquery in this code?

When I execute the following code, I'm getting results such as: ID column1 column2 34 NULL NULL 34 Org13 Org13 36 NULL NULL 36 NULL Org2 36 Org4 NULL 41 NULL NULL 41 NULL Org5 41 Org3 NULL I want my results to look like: ID column1 column2 34 Org13 Org13 36 Org4 Org2 41 Org3 Org5 I've got two tables: Table1 and Ta...

sub query with parameter?

Hi Pros, I'd like to ask help on this small query of mine. Im doing a query and a sub query on my sub query i want to have it parameterized. Is there a way to do it? Please see my query script. select sum(issue) as [Issue], sum(nonissue) as [NonIssue] from ( AS select case when isissue = 1 then 1 else 0 end as 'issue', ...

How to break subquery into 2 simple query

Hi , I am trying some way to optimize following sql statement: exe_sql "DELETE FROM tblEvent_type WHERE eguid in (SELECT rowid FROM tblEvent_basic WHERE sguid=11);"; exe_sql "DELETE FROM tblEvent_group WHERE eguid in (SELECT rowid FROM tblEvent_basic WHERE sguid=11);"; It is said that sqlite3 did not perform well in subquery and not...

MySQL: Sum Subqueried Columns

Is there an elegant way to do this in MySQL: SELECT (subquery1) AS s1, (subquery2) AS s2, (s1+s2) AS s3 or must I resort to SELECT (subquery1) AS s1, (subquery2) AS s2, ((subquery1)+(subquery2)) AS s3 ? Thanks EDIT: both subqueries yield integer results ...

Intersection of result sets: Existing API?

Suppose I run k-many sub-queries against e.g. tables in a database(s), and the results from each query share a common field e.g. a string, or uuid value, which shares meaning across these tables. Assuming I can have the queries returned sorted, are there API's which exists in languages already which allow me to find the intersection of ...

Help required to optimize LINQ query

I am looking to optimize my LINQ query because although it works right, the SQL it generates is convoluted and inefficient... Basically, I am looking to select customers (as CustomerDisplay objects) who ordered the required product (reqdProdId), and are registered with a credit card number (stored as a row in RegisteredCustomer table wi...

selecting first column from all tables in a mysql database

i have a column named "name" which is present in all tables in mysql database. I wanted to list all the names in all tables so i used the following query select name from (SELECT table_name FROM information_schema.tables WHERE table_type='BASE TABLE') as abc But it did not work for me and instead it returned me the table_name column ...

combining two queries

This first query grabs all the transaction_details for a particular date range This is tricky though, I actually need the sum of sums in this query if that makes any sense. SELECT td.transaction_id, sum( IF( coalesce(ti.na, -1) = 0 AND coalesce(ti.special_info_type, -1) = 0 AND coalesce(ti.item_type, '') = 'P' AND...

Nhibernate: left outer join on subquery

Update take 2 here is the two queries i'm working with (paging is omitted in both queries) i'd like to get the following query SELECT * FROM product LEFT OUTER JOIN ( SELECT * FROM Cart LEFT OUTER JOIN cartproducts ON Cart.Id = cartproducts.Cart_id WHERE Cart.username = 'user' ) AS CartFiltered ON produ...

Subquery in a Left Join

Hi all LEFT JOIN teams ON teams.id = (SELECT team_id FROM auth_users WHERE id = offers.user_id) Gives me all the columns of the teams table, but converts everything to NULL. LEFT JOIN teams ON teams.id = 1 works like a charm When i do SELECT (SELECT team_id FROM auth_users WHERE id = offers.user_id) as team_id team_id will be ...

MySQL where clause and ordering by avg() as a sub query

Although I can group and order by on an aliased sub query, I can't use the alias in a where clause. Do I need to use a join instead? Works: SELECT entries.*, (SELECT avg(value) FROM `ratings` WHERE ratings.entry_id = entries.id) as avg_rating FROM `entries` ORDER BY avg_rating DESC Fails ("unknown column 'avg_rating' i...

Mysql Case returns wrong value

On one server I use mysql version 5.0.45 and when running the following query it returns 1 if boolvalue='True' AND 2.2 else, just as it should. SELECT Q.value FROM ( SELECT (CASE table.boolvalue='True' WHEN 1 THEN 1 ELSE 2.2 END) AS value FROM table ) Q; On another server (webhosting) I use mysql version 5.0.22 and when running the sa...

NHibernate ICriteria subqueries with ISet<T> properties: Is this correct?

Given a class A related to another class B in a one-to-many relationship, how can you create an NHibernate query (using the Criteria API--no HQL) to find all objects A who "have none" of the related objects B? Specifics follow, but I wanted to get the question out there first. I have two model classes, Sample and SampleDiagnosis. Sam...