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