subquery

MSSQL JOIN ON GROUP BY is too slow

I have the following query in MSSQL SELECT TOP 50 CustomerID FROM Ratings WHERE CustomerID != 915 AND MovieID IN (SELECT DISTINCT MovieID FROM Ratings WHERE CustomerID = 915) GROUP BY CustomerID ORDER BY count(*) DESC It is super fast. When I try to use it in a subquery like this. SELECT * FROM Ratings WHERE MovieID = 1 AND CustomerI...

Compare 2 values of different types inside of subquery

I am using a MS SQL db and I have 3 tables: 'base_info', 'messages', 'config' bases: ID Name NameNum ==================================== 1 Home 101 2 Castle 102 3 Car 103 messages: ID Signal RecBy HQ ============================ 111 120 Home 1 111 110 Castle 1 111 125 Car...

subquery pull - All lines

I am pulling information from a table with multiple lines that all corrorpond to one ticket. If anywhere on the ticket I see a charge for MIlk I want to see all lines associated with that ticket#. I tried to write a subquery but that has not worked for me. Any ideas?? Example: Item Cost Yams 1.0 Beans 2.0 Milk 5.0 Yams 1.0 Bea...

SQL SELECT: combining and grouping data between three tables using subqueries

Sorry for a long question and not a very descriptive title, but my problem is very difficult to explain briefly. I have three database tables: TABLE A: AID PK STATUS VARCHAR TABLE B: BID PK AID FK CID FK TABLE C: CID PK CREATIONTIME DATE For each STATUS = 'OK' row in table A I want to find the corresponding row in C w...

Advanced multiple join in subquery using LINQ

I have spent the afternoon trying to wrap my mind around how to translate the following query into LINQ, but I can't quite get there. declare @productId int; set @productId = 3212; select * from InformationData data where productId = @productId and orgId = 1 and exists( select id from ( select coalesce(id1.id, id2.id, id3.id)...

MySQL/SQL: Update with correlated subquery from the updated table itself

I have a generic question that I will try to explain using an example. Say I have a table with the fields: "id", "name", "category", "appearances" and "ratio" The idea is that I have several items, each related to a single category and "appears" several times. The ratio field should include the percentage of each item's appearances out...

How Can I write query in Subsonic for Subqueires.

Hi, How can I do the following query in subsonic 2.2 select Table1.Id, Table1.Name, Table1.Age from Table1 where Table1.Id = ( select Max(T.Id) from Table1 T Where T.Age = 20 ) Can one can provide me with example. Thanks. nRk ...

NHibernate - joining on a subquery using ICriteria

I have a SQL query that I need to represent using NHibernate's ICriteria API. SELECT u.Id as Id, u.Login as Login, u.FirstName as FirstName, u.LastName as LastName, gm.UserGroupId_FK as UserGroupId, inner.Data1, inner.Data2, inner.Data3 FROM dbo.User u inner join dbo.GroupMember gm on u.Id = ...

T-SQL Subquery Max(Date) and Joins

I'm trying to join multiple tables, but one of the tables has multiple records for a partid with different dates. I want to get the record with the most recent date. Here are some example tables: Table: MyParts Partid Partnumber Description 1 ABC-123 Pipe 2 ABC-124 Handle 3 ABC-125 Light Table:...

SubQueries in MS ACCESS: selecting only one record per "person" per date.

I am using a table called analyzed in Microsoft Access. It has many fields but the three ones being used for filtering in this case are analyzed.readings_miu_id, analyzed.ReadDate, analyzed.ReadTime. I need to pull the records from the "analyzed" table where readings_miu_id are grouped together then sorted by ReadDate but showing only th...

How can I select only one record per “person”, per date with an inner join in an MS Access query?

I need to pull data from two tables: Neptune_FN_Analysis and Neptune_prem There will be 3 fields called readings_miu_id (comparable to a persons name or item #), ReadDate, ReadTime (all of which are in Neptune_FN_Analysis). Some readings_miu_ids have multiple ReadTimes for multiple days but I only want to pull the "last time" entered per...

MySQL: Calculated Column Based on Two Calculated Columns

I'm trying to do a rather complicated SELECT computation that I will generalize: Main query is a wildcard select for a table One subquery does a COUNT() of all items based on a condition (this works fine) Another subquery does a SUM() of numbers in a column based on another condition. This also works correctly, except when no records m...

Count(*) in a subquery

I need the number of line items per Order. In this example, order 10 has 0 and order 11 has 2 lines. IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.OrderDetail') AND type in (N'U')) DROP TABLE OrderDetail IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.OrderHeader') AND type in (N'U')) D...

understanding JasperReports and JRBeanCollectionDataSource...

I now have my jasper reports working from my JRBeancollectionDataSource in my code! I am just a bit confused about some things... When I am designing reports in iReport, I create the fields from a query, which I can do fine and all, since when I am actually running the query I'm using a a code JRBeanCollectionDataSource and so is the h...

OrderHeader.*,COUNT(OrderDetail) from a subquery

An earlier question was answered about getting the OrderID and the number of associated line items. Now I would like to get the rest of the fields in the OrderHeader row. IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.OrderDetail') AND type in (N'U')) DROP TABLE OrderDetail IF EXISTS (SELECT * FROM sys.objects ...

Oracle: Using subquery in a trigger

How can I work around the Oracle's limitation of not allowing subqueries in triggers. Here's an example trigger I'm trying to create, but am unable to because I can't use a subquery. CREATE OR REPLACE TRIGGER trigger_w_subquery AFTER UPDATE OR INSERT ON project_archiving FOR EACH ROW WHEN (old.archiving_status <> new.archiving_status ...

Is there an implementation of this query-syntax in TSQL?

In some SQL dialects, you can state (something as): SELECT * FROM SomeTable WHERE (val1,val2) IN (SELECT val1,val2 FROM SomeOtherTable) But I don't know how to do that in the TSQL (sql server 2k) I am using. I am aware of (and using for now) workarounds like using joins or concatenated values, but is there some syntax in TSQL I...

Why does adding count(*) to a select statement force a row to exist in a subquery?

On Oracle 9i, why does the following produce the result 'abc' select 'abc ' || (select txt from (select 'xyz' as txt from dual where 1=2)) from dual while this produces 'abc xyz': select 'abc ' || (select txt from (select count(*), 'xyz' as txt from dual where 1=2)) from dual Why does adding count(*) to the subquery r...

Correlated subqueries in MySql - how to evaluate outer query first??

Hi all! I am having problems with subqueries in MySql. I have a table containing user groups. The columns are id, name and the properties with a comment describing each row: (Id is INT, Name VARCHAR, all other TINYINT(1) (boolean that is) ID | Name | login | post | manage 1 user 1 0 0 ...

Can you define "literal" tables in SQL?

Is there any SQL subquery syntax that lets you define, literally, a temporary table? For example, something like SELECT MAX(count) AS max, COUNT(*) AS count FROM ( (1 AS id, 7 AS count), (2, 6), (3, 13), (4, 12), (5, 9) ) AS mytable INNER JOIN someothertable ON someothertable.id=mytable.id This would sav...