Optimize "not in" query
Please help optimize this query: SELECT ts.SiteId, COUNT(ts.SiteId) AS Count FROM ts WHERE ts.SiteId not in (SELECT ts.SiteId FROM ts WHERE ts.uniqueid = 'xxx') GROUP BY ts.SiteId ORDER BY Count DESC ...
query
how to reverse the order of words in query or c#
i have stored in the database as location India,Tamilnadu,Chennai,Annanagar while i bind in the grid view it ll be displaying as 'India,Tamilnadu,Chennai,Annanagar' this format. but i need to be displayed as 'Annanagar,Chennai,Tamilnadu,India' in this format. how to perform this reverse order in query or in c# note: this is stored ...
When doing a UNION in mysql how can I do a where on the results
Hi I am doing a union over several tables. It's a little long but works! (SELECT user_id,added_date,group_id,'joined',0,0,'' FROM group_members WHERE status = 1) UNION (SELECT user_id,added_date,object_id,'made a comment',0,0,'' FROM comments WHERE object_type = 11 AND status = 1) UNION (SELECT user_id,added_date,group_id,'made the even...
Is it possible to query against these tables?
I have table one which looks like this. And I want to get data like 1.2, "My name is " , 1.6, "Earl" ,12345, "Rock Hard Awesome" I don't think it is possible with this schema but wanted to see if I am wrong. I tried a cross join but got ever possibility not just the actual values. What is supposed to be accomplished is the values tabl...
Need some help with a NHibernate Query
Hi all Say I got 3 entities: Business, Employee and Payment. A payment has a foreign key to an Employee, while the Employee has an foreign key to a business. Now, I want to create a query which gives me all payments for a given business. I really don't have a clue about how to do this - I guess I want something like: mySession.CreateC...
SQL query sample
Hi, my table is look like this order.. I would like count the TermID's with these conditions; If it's possible also want to get resultset with these headers - TermID must have close or open status - TermID Status must turn to Open (close to open) - Status Date (open status) must be bigger date than close status date with my regards, ...
Can I select a set of rows from a table and directly insert that into a table or the same table in SQL?
Hi all I was just curious if I could do something like - insert into Employee ( Select * from Employee where EmployeeId=1) I just felt the need to do this a lot of times...so just was curious if there was any way to achieve it.. ...
getting values by time difference in SQL
I want to get the difference of two values within a time frame. so I have a table like this Data Table TimeStamp DataValue 2010-06-01 21 2010-06-03 33 2010-06-05 44 So I want to first get all data over the last month which I can do with something like. ([TimeStamp] < GETDATE()-0 and ([TimeStamp] > GETDAT...
Combining two-part SQL query into one query
Hello, I have a SQL query that I'm currently solving by doing two queries. I am wondering if there is a way to do it in a single query that makes it more efficient. Consider two tables: Transaction_Entries table and Transactions, each one defined below: Transactions - id - reference_number (varchar) Transaction_Entries - id - acco...
SQL alert for a stored procedure?
I have a SQL 2005 setup and am rather new :) Been cracking at this for a few hours and I just need some help. I have been able to setup alerts successfully for the standard "SQL server performance events", its fun. So I already have email alerts working. However I need the alert thing to run a stored procedure I have created, and depe...
Replace BIT values with some text
I have a BIT column with gender (0,1) and want to replace 0 and 1 in the resulting view with the words "man" and "woman". Can i do this right in the view with some system finction or i have to write my own function to do that? ...
Sql query listing Fathers and childs with joins, how to distinct them?
Having those tables: table_n1: | t1_id | t1_name | | 1 | foo | table_n2: | t2_id | t1_id | t2_name | | 1 | 1 | bar | I need a query that gives me two result: | names | | foo | | foo / bar | But i cant figure out the right way. I wrote this one: SELECT CONCAT_WS(' / ', table_n1.t1_name, table_...
How extension methods work in background ?
I am just cuirous about behind of extension method mechanism.Some questions and answer appear in my mind. MyClass.OrderBy(x=>x.a).OrderBy(x=>x.b); I was guessing that mechanism was first orderby method works and order them by a member then returns sorted items in IEnumarable interface then next Orderby method of IEnumarable Order them...
SQL query root parent child records
Hi, We have nested folders with parent-child relationship. We use MySQL MyISAM DB. The data is stored in the DB in the following manner. Every time a child folder is created in the nested structure, the previous parentID is added. I want to get the RootFolderID of a folder which is added in the hierarchy as tabulated below. FoldID Par...
how to show only even or odd rows in sql server 2008 ?
hi i have a table MEN in sql server 2008 that contain 150 rows. how i can show only the even or only the odd rows ? thank's in advance ...
Writing to mysql databse PHP. How to update value in field??
I have a field in my mysql table that I want to update. Example: In my database the field amount has a value of 4 and I want to add 6 to the value. How would I add 6 to the existing value??? This is what I have so far but this just deletes the existing value and adds a new one: mysql_send("UPDATE peopletable SET amount='$amount' WHER...
sql select from a large number of IDs
I have a table, Foo. I run a query on Foo to get the ids from a subset of Foo. I then want to run a more complicated set of queries, but only on those IDs. Is there an efficient way to do this? The best I can think of is creating a query such as: SELECT ... --complicated stuff WHERE ... --more stuff AND id IN (1, 2, 3, 9, 413, 4324, ....
select query related problem
i have interest rate and amount in a table where interest rate are ranged in different values like 4.5,4.6,5.2,5.6 etc. i want to get sum of amounts classified by interest rate where interest rate will be separated by .25. for example all amount having interest rate 1.25,1.3,1.4 will be in one group and 1.5,1.67,1.9 will be in another ...
Speed up a query, simple inner join with one large table and one small table.
I have a table T1 with 60 rows and 5 columns: ID1, ID2, info1, info2, info3. I have a table T2 with 1.2 million rows and another 5 columns: ID3, ID2, info4, info5, info6. I want to get (ID1, ID2, info4, info5, info6) from all the rows where the ID2s match up. Currently my query looks like this: SELECT T1.ID1, T2.ID2, T2.info4,...
How to filter rows on a complex filter
I have these rows in a table ID Name Price Delivery == ==== ===== ======== 1 apple 1 1 2 apple 3 2 3 apple 6 3 4 apple 9 4 5 orange 4 6 6 orange 5 7 I want to have the price at the third delivery (Delivery=3) or the last price if there's no third delivery. It would...