I need to do the following for the purposes of paging a query in nHibernate: Select count(*) from (Select e.ID,e.Name from Object as e where...) I have tried the following, select count(*) from Object e where e = (Select distinct e.ID,e.Name from ...) and I get an nHibernate Exception saying I cannot convert Object to int32. Any...
hi, I'm on SQL Server 2005 and I am getting an error which I am pretty sure should not be getting. Msg 512, Level 16, State 1, Procedure spGetSavedSearchesByAdminUser, Line 8 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. I am fol...
In certain areas in my application I need data from several tables in the database (I have an application, the application has many attributes, each attribute has several definitions, and each definition has a value). I need the data from all these tables for an application. Should I use a view (one that would be rather large) or subque...
I have SQL data that looks like this events id name capacity 1 Cooking 10 2 Swimming 20 3 Archery 15 registrants id name 1 Jimmy 2 Billy 3 Sally registrant_event registrant_id event_id 1 3 2 3 3 2 I would like to select all of the fields in 'ev...
I have three related tables "A(id, val)", "B(id, val)", and a link table with a value "AB(aid, bid, val)" I am querying against B to bring back A values, for example: SELECT A.* FROM A INNER JOIN AB ON A.id = AB.aid INNER JOIN B ON AB.bid = B.id WHERE B.val = 'foo'; Every A has many B's and every B has many A's. And the catch that ...
I'm creating a really complex dynamic sql, it's got to return one row per user, but now I have to join against a one to many table. I do an outer join to make sure I get at least one row back (and can check for null to see if there's data in that table) but I have to make sure I only get one row back from this outer join part if there's ...
I have a list of stores, departments within the stores, and sales for each department, like so (created using max(sales) in a subquery, but that's not terribly important here I don't think): toronto baskets 500 vancouver baskets 350 halifax baskets 100 toronto noodles 275 vancouver noodles 390 halifax noodles 120 halifax ...
I used to do this: SELECT layerID FROM layers WHERE ownerID = ? AND collectionID = ? Which would give me an array of layerID's, and then I'd loop and do this for each one: SELECT DATA FROM drawings WHERE layerID = ? And it all worked fine. So now I'm trying to do it in one step, so I try this: SELECT DATA , layerID FROM drawings W...
I have a DataTable that queries out something like below usergroupid...userid......username 1.............1...........John 1.............2...........Lisa 2.............3...........Nathan 3.............4...........Tim What I'm trying to do is write a LINQ statement that will return an array of UserGroup instances. The UserGroup class h...
I'm using this query to get all employees of {clients with name starting with lowercase "a"}: SELECT * FROM employees WHERE client_id IN (SELECT id FROM clients WHERE name LIKE 'a%') Column employees.client_id is an int, with INDEX client_id (index_id). The subquery should IMHO return a list of id-s, which is then used in the WHERE...
Coming from another question of mine where I learnt not to EVER use db queries within loops I consequently have to learn how to fetch all the data in a convenient way before I loop through it. Let's say I have two tables 'scales' and 'items'. Each item in items belongs to one scale in scales and is linked with a foreign key (scaleID). I...
Hello, I have a MySQL query in which I want to include a list of ID's from another table. On the website, people are able to add certain items, and people can then add those items to their favourites. I basically want to get the list of ID's of people who have favourited that item (this is a bit simplified, but this is what it boils dow...
Observation: This is a repost of a question I asked at the Hibernate forum but got no response. I have a simple graph structure like this (in MySQL): create table Node( id int not null auto_increment, name varchar(255), primary key(id) ) engine=InnoDB; create table Edge( source int not null, target int not null, cost...
I have a query like this: SELECT t1.id, (SELECT COUNT(t2.id) FROM t2 WHERE t2.id = t1.id ) as num_things FROM t1 WHERE num_things = 5; The goal is to get the id of all the elements that appear 5 times in the other table. However, I get this error: ERROR: column "num_things" does not exist SQL state: 42703 I'...
I'm trying to figure out how to exclude items from a select statement from table A using an exclusion list from table B. The catch is that I'm excluding based on the prefix of a field. So a field value maybe "FORD Muffler" and to exclude it from a basic query I would do: SELECT FieldName FROM TableName WHERE UPPER(ColumnName) NOT LIK...
I need to get 2 summed figures however im having issues as one will be for total orders and one is for orders incomplete. These use the same initial query however the orders incomplete has an additional where clause. Can these be put into a query so that i just get the 2 columns. I have done inner queries before but i have never done one...
I misremembered what the key was for this Templates table and therefore I added the wrong field as a foreign key. Now I need to add the foreign key and I want to populate its values based on this other field that is already populated. I started trying to do this with an update statement, but I'm not sure how to do it. Part of my schema:...
I need to query this DB to get each row, but also the SUM of one of the column values of the results. I could use php to get the total value, but then I'd need to run two loops, one to get the total (which goes at the top above the results). So I'd prefer the query to catch it and just make a "total" row, but the only way I've gotten it ...
I'm trying to convert a single-columned subquery into a command-separated VARCHAR-typed list of values. This is identical to this question, but for Oracle rather than SQL Server or MySQL. ...
Example Query: select * from A join B on A.ID = B.SOMEVALUE where A.VALUE="something" and B.ID = (select ID from B where SOMEVALUE = A.ID and THISDATE = (select max(SOMEDATE) from B where ...)) so, if you can read SQL you should see that I am doing a couple correlated subqueries to narrow down the results of th...