I have decided in my table that I no longer want to record start and finish times, but rather just a start time and a duration in minutes. How can I update my table so that my new column has its values inserted based on the existing data? my attempt below yields the error: You can't specify target table 'lesson' for update in FROM c...
In my project, Lines can be grouped and a Group has a type which can be either Crossing (1) or Parallel (2). I need to find all lines which has at least one group of a specified type (in this case, 1). The Id of a given line can be either on column LineA or LineB of a group. Here is where i got so far: Criteria crit = session.CreateCrit...
Hi, I am making a website where users add the place where they have visited. There is 4 main tables users (user_id,name ) places (place_id,type_id,place_name, city_id) user_place (user_id,place_id, date ) city(city_id,city_name) Now I need to take with one query all the places which are type of 2 (where type=2) where the given user...
this is an attempted fix to a crystal reports use of 2 sub reports! I have a query that joins 3 tables, and I wanted to use a pair of sub selects that bring in the same new table. Here is the first of the two columns in script: SELECT ea."LOC_ID", lo."DESCR", ea."PEGSTRIP", ea."ENTITY_OWNER" , ea."PCT_OWNERSHIP", ea."BEG_BAL", ea."ADD...
I'd like to create a LINQ query that returns the sum of all quantities for a given productnumber for a parent account and all it's child accounts. I have a table of products by account number in which each row also contains a qty and the parent account number: PartNumber AccountNumber ParentAccountNumber Qty ---------- ------...
Hi, I'm having this Mysql query, It works: SELECT nom ,prenom ,(SELECT GROUP_CONCAT(category_en) FROM (SELECT DISTINCT category_en FROM categories c WHERE id IN (SELECT DISTINCT category_id FROM m3allems_to_categories m2c WHERE m3allem_id = 37) ) cS ) categories ,(SELECT GRO...
I have this query: select acc_num from (select distinct ac_outer.acc_num, ac_outer.owner from ac_tab ac_outer where (ac_outer.owner = '1234567') and ac_outer.owner = (select sq.owner from (select a1.owner from ac_tab a1 ...
This problem may seem like school work, but it isn't. At best it is self-imposed school work. I encourage any teachers to take is as an example if they wish. "First past the post" elections are single-round, meaning that whoever gets the most votes win, no second rounds. Suppose a table for an election. CREATE TABLE ElectionResults ( ...
I have a working query that retrieves the data that I need, but unfortunately it is painfully slow (runs over 3 minutes). I have indexes in place, but I think the problem is the multiple dependent subqueries. I've been trying to rewrite the query using joins but I can't seem to get it to work. Any help would be greatly appreciated. The ...
Hi, Please explain. a) "subquery factoring" is used to replace a non-correlated subquery. What about correlated subquery? Is there any way to move a correlated sub-query to 'WITH' clause section? b) are "subquery" "subquery factoring" executed exactly once? c) "subquery" vs "subquery factoring" which one is better Thank you. ...
This is a contrived example using SQL Server 2008. I'm essentially storing a list of ids in an xml column in a table: temp (bigint id, xml ids) I want to join the table itself to the xml nodes. So far I have: select * from temp x join ( select x.id , ids.id.value('@value', 'bigint') zid from temp t cross apply ids....
I'm in the process of normalizing a few tables and I've added a surrogate primary key to a table called Exams which holds exam titles. Previously, the child tables would just use the entire name of the exam as the FK entry. Now that I've added a autonumbered field to the table, I want to update the entries that use it such as the tabl...
Hi, I am trying to write a view that has 3 columns: Planet, Moon, and Largest. The view is meant to show planets, their moons, and a Yes or No column indicating whether or not it is the largest moon for the planet. Only one Basetable is used, and the columns I am referencing are moonPlanetOrbit (only not null if bodyType is = to 'Moon'...
I have a table cats with 42,795,120 rows. Apparently this is a lot of rows. So when I do: /* owner_cats is a many-to-many join table */ DELETE FROM cats WHERE cats.id_cat IN ( SELECT owner_cats.id_cat FROM owner_cats WHERE owner_cats.id_owner = 1) the query times out :( (edit: I need to increase my CommandTimeout value, default is ...
I have a correlated subquery that will return a list of quantities, but I need the highest quantity, and only the highest. So I tried to introduce an order by and a LIMIT of 1 to achieve this, but MySQL throws an error stating it doesn't yet support limits in subqueries. Any thoughts on how to work around this? SELECT Product.Name, Prod...
Hi All, I've been doing some work evaluating NHibernate for an upcoming project and am working through some use cases to see how it performs. I haven't yet been able to find a way to express the following query using the Criteri API. Two fairly basic tables (cut down for the purpose of this example) CREATE TABLE Person ( PersonNo ...