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. ...
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...
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 ...
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...
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...
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...
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...
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...
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', ...
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...
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 ...
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 ...
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...
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 ...
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...
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...
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 ...
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...
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...
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...