query

How can I make this query easier to write

I have a query that pulls up questions from one table and answers from another. SELECT questions.question, questions.answers, (SELECT COUNT(answer) FROM answers WHERE question_id = 1 AND answer = 1 GROUP BY answer) as ans1, (SELECT COUNT(answer) FROM answers WHERE question_id = 1 AND answer = 2 GROUP BY...

Vertical to Horizontal?

Hi again, I have a PostgreSQL table that looks like this: A -> B A -> C A -> G A -> H B -> O B -> K Where "->" separates two columns where the first points to the second (hyperlinks). Now I would like to take all distinct values in the first column and assign them an ARRAY containing all values to which they point to in the second co...

How to set more than 2 Expression in Expression.Or

I want to create a query which has more than 3-4 Expression.Or ? But Expression.Or just let me to add two Expressions inside it. if (!string.IsNullOrEmpty(keyword)) query .Add(Expression.Or( Expression.Like("Name", keyword, MatchMode.Anywhere), ...

MySQL Query - getting missing records when using group-by

I have a query : select score, count(1) as 'NumStudents' from testresults where testid = 'mytestid' group by score order by score where testresults table contains the performances of students in a test. A sample result looks like the following, assuming maximum marks of the test is 10. score, NumStudents 0 10 1 20 2 12 3...

SQL Sub-Query vs Join Confusion

I have a database which is in Access (you can get it link text). If I run SELECT DISTINCT Spl.Spl_No, Spl.Spl_Name FROM Spl INNER JOIN Del ON Spl.Spl_No = Del.Spl_No WHERE Del.Item_Name <> 'Compass' It provides the names of the suppliers that have never delivered a compass. However you can supposedly do this with a sub-query. So f...

linq to sql case query

Im having problems building a query with the linq to sql data query expression in c#. What I'm trying to ultimately do is based on this pseudo-code expression. public IQueryable<CTest> searchRecords(string category, string searchString, DateTime startDate, DateTime endDate, int searchType, int searchType2) { //-Sear...

SQL Server 2000 - What really is the "Actual Number of Rows" ?

Hello, I have an SQL Server 2000 query which performs an clustered index scan and displays a very high number of rows. For a table where I have 260.000 records, the actual number of rows displayed in the plan execution is ... 34.000.000. Does this makes sense? What am I misunderstanding? Thanks. ...

$data findAll query in CakePHP checking values from second table

Hello there, I have been stumped by CakePHP in how to query the DB in CakePHP and return things to $data only when the $data query table [id] has a matching [sub_id] in a second table a standard query: $data = $this->Table1->findAll(array("Table1.deleted" => "0"), null, "Table1.id DESC", 25, null, 1); But I want to only have values p...

Query building in a database agnostic way

In a C++ application that can use just about any relational database, what would be the best way of generating queries that can be easily extended to allow for a database engine's eccentricities? In other words, the code may need to retrieve data in a way that is not consistent among the various database engines. What's the best way ...

T-SQL: Selecting rows to delete via joins

Scenario: Let's say I have two tables, TableA and TableB. TableB's primary key is a single column (BId), and is a foreign key column in TableA. In my situation, I want to remove all rows in TableA that are linked with specific rows in TableB: Can I do that through joins? Delete all rows that are pulled in from the joins? DELETE FROM ...

Solving JPA query finding the last entry in connected list

Following class structure is given: class Job { String description; Collection<JobHistory> history; } class JobHistory { Date assignDate; User jobOwner; } class JobOwner { String name; String id; } This class-structure is accessible on the db via JPA. In the DAO-Layer I can write queries in JPA syntax. The...

SQL: Get value at index in binary value

Is there a SQL command that could be used in a query, stored procedure, function, that would work against a Binary Type similar to the following C# code? if (someBinaryArray[index] == 0) { ... I'm wanting to check if an index of a position in the binary is a certain value instead of pulling the entire array down and doing the compari...

SQL - Use results of a query as basis for two other queries in one statement

I'm doing a probability calculation. I have a query to calculate the total number of times an event occurs. From these events, I want to get the number of times a sub-event occurs. The query to get the total events is 25 lines long and I don't want to just copy + paste it twice. I want to do two things to this query: calculate the numb...

Using a variable in a query

I am trying to set up a query for my dataset in C# using a variable for the filter. For example I am trying to only display a specific account number and his balance, with a local variable being the account number used as a filter for that exact one. Am I going about this the wrong way? I am in no stretch of the imagination a real progr...

Ftp GetFileSize randomly throws FTP error 503 (Bad Sequence of Commands)

To get the file size for every file in a list of files, I'm using the following code: foreach (String f in files) { UriBuilder ftpUri = new UriBuilder("ftp", ftpServer, -1, ftpPfadZuLogDateien + "/" + f); FtpWebRequest ftpclientRequest1 = (FtpWebRequest)WebRequest.Create(ftpUri.Uri); ftpclientRequest1.Method = WebRequestMethods.Ft...

How to make Lucene match all words in query?

I am using Lucene to allow a user to search for words in a large number of documents. Lucene seems to default to returning all documents containing any of the words entered. Is it possible to change this behaviour? I know that '+' can be use to force a term to be included but I would like to make that the default action. Ideally I woul...

How can I get column names from a table?

I need to query the database to get the column/field names, not to be confused with data in the table. For example, if I have a table named EVENT_LOG that contains eventID, eventType, eventDesc, and eventTime, then I would want to retrieve those field names from the query and nothing else. Please help? Thanks in advance. ...

Does adding 'LIMIT 1' to MySQL queries make them faster when you know there will only be 1 result?

When I add LIMIT 1 to a MySQL query, does it stop the search after it finds 1 result (thus making it faster) or does it still fetch all of the results and truncate at the end? ...

Linq to SQL Queries determined at runtime

The number of query conditions is determined by user selections at runtime, ie var results= from r in db.Table where condition A && condition B && ... condition XX... Is the best way to handle this to build a string variable and append to it or is there another way? ...

How to obtain the IDbCommand output to the DB? (.NET)

I have a SqlCommand object, and I execute its ExecuteNonQuery method. The query fails, but I can't see how to obtain the query to debug it. Could you help me? Update I am currently using Wireshark (tcpdump) to read the command, but I think that its not the best solution Update The CommandText property only provides the Stored Procedure...