How do I do the following SQL in LINQ? I am actually using LINQ to NHibernate (but maybe it's not possible in NHibernate LINQ due to embedded lambda expression I think). But I want to know generally how to do it in LINQ. I’ve never came across this situation before. SELECT c.CustomerID, c.CustomerName --etc FROM Customers c INN...
Hi, Is it possible to write a cross server select query using MySQL Client. Basically the setup is like follows. Server IP Database --------- -------- 1.2.3.4 Test a.b.c.d Test I want to write a query that will select rows from a table in the Test Database on 1.2.3.4 and inse...
I have counter for unique column in my table. Now I have list where I have column name (which is unique) and counter (which contains some integer value). I know that it is possible to insert multiple lines, I just could make query containing all those items on the list, but problem is that if there is already data in unique field. In th...
Hi All, I've got a very large table (~100Million Records) in MySQL that contains information about files. One of the pieces of information is the modified date of each file. I need to write a query that will count the number of files that fit into specified date ranges. To do that I made a small table that specifies these ranges (all i...
[ edit ] For the record, here's the problem portion of the query, which is now working: SELECT m.groupNum, t.ea, ( t.ea - ( t.ea * m.margin )) AS estCost, (( t.ea - ( t.ea * m.margin )) * t.quantity ) AS salesSub, ((( t.ea - ( t.ea * m.margin )) * t.quantity ) / ( SELECT SUM(( t2.ea - ( t2.ea * m.margin )) * t2.quantity ...
I have an array of user ids in a query from Database A, Table A (AA). I have the main user database in Database B, Table A (BA). For each user id returned in my result array from AA, I want to retrieve the first and last name of that user id from BA. Different user accounts control each database. Unfortunately each login cannot have ...
Hi all, I was wondering if there was a way to get the number of results from a MySQL query, and at the same time limit the results. The way pagination works (as I understand it), first I do something like query = SELECT COUNT(*) FROM `table` WHERE `some_condition` After I get the num_rows(query), I have the number of results. But th...
Here is my query: select word_id, count(sentence_id) from sentence_word group by word_id having count(sentence_id) > 100; The table sentenceword contains 3 fields, wordid, sentenceid and a primary key id. It has 350k+ rows. This query takes a whopping 85 seconds and I'm wondering (hoping, praying?) there is a faster way to find all...
Hello all, I am trying to get the latest record inserted in a SQL table based on its CreatedDate. For instance, in the table below, I would like to get the third row. A B C 2009-05-04 19:30:52.847 A B D 2009-05-04 19:30:55.050 A B E 2009-05-04 19:30:57.003 I have a working query, but I am wondering if there is better way to ac...
For example, if I wanted to know the current value of the quoted_identifier server option, is there a query that can give me this information? ...
I am trying to trieve a list of files where a field ARTICLE_NO is a certain number. At the moment, I have a files table, with columns ARTICLE_NO, FILENAME and USERNAME. There is one record, with values 1, x.txt and user respectively. $pk is assigned to 1 in my php code. However, the following code only produces NULL, and I am unsure why....
I have some very simple XML: <properties> <property> <name>BobFish</name> <explaination>Bob is a fish.</explaination> </property> <property> <name>DaveFish</name> <explaination>Dave is a fish.</explaination> </property> I want to query if from ASP. Something like: Response.Write (GetExplaination("BobFish")) This is the functi...
We want to be able to select top N rows using a SQL Query. The target database could be Oracle or MySQL. Is there an elegant approach to this? (Needless to say, we're dealing with sorted data here.) ...
Is there a standard way of encoding SQL queries as XML? I mean something like select name from users where name like 'P%' group by name order by name desc might encode as (my 5-minute mockup, probably bobbins)... <?xml version="1.0" encoding="UTF-8"?> <query> <select> <table name="users"> <column name="name"/>...
Hello, I've been doing a lot of implementation in Linq recently, and it suddenly occurred to me that as great as it is, I haven't really explored any other options. Any thoughts? ...
Right now I have this SQL query which is valid but always times out: SELECT ( SELECT (MAX(WP_ODOMETER) - MIN(WP_ODOMETER)) / DATEDIFF(DAY, MIN(WP_DATETIME), MAX(WP_DATETIME)) FROM WAYPOINTS WHERE WP_DATETIME BETWEEN DATEADD(DAY,-14,GETDATE()) AND GETDATE() AND WP_VTDID = 'L088' ) AS MAXD, ( SELECT MAX(WP_ODOMETER) FROM WAYPOINTS WHER...
Dear All I have a MySQL db and inside it a table called ProductMaster with 6 fields "Product_Id,Product_Name,Model,Opening_date,Closing_Date,Status". Opening_Date and Closing Date Can accept null values.So some records has values for this field I have the below query to display records from this table. "select Product_Id,Product_Name,...
In SQL Server, TOP may be used to return the first n number of rows in a query. For example, SELECT TOP 100 * FROM users ORDER BY id might be used to return the first 100 people that registered for a site. (This is not necessarily the best way, I am just using it as an example). My question is - What is the equivalent to TOP in other da...
I have a database which is 6GB in size, with a multitude of tables however smaller queries seem to have the most problems, and want to know what can be done to optimise them for example there is a Stock, Items and Order Table. The Stock table is the items in stock this has around 100,000 records within with 25 fields storing ProductCode,...
Hi, I'm trying to get a list of all check-ins (limited / ordered by date) via the TFS query editor in Visual Studio Team Explorer. I can make a query that lists all bugs, sprint backlog item or product backlog item, but I can't find the actual check-in. Is it's possible or should I make (SQL) queries directly on the database. Ideas?...