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...
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...
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...
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...
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)...
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...
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 ...
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 = ...
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:...
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...
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...
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...
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...
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...
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 ...
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 ...
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...
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...
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 ...
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...