query

SQL Subquery

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...

A SQL query that replaces null values.

I need a SQL query that returns ContactDate, SortName, City, ContactType, and Summary from the tables below. If any value is null, I need it to return the text “No Entry”. ContactTable ContactID ContactDate UserID Summary ContactType SortName UserTable UserID FirstName LastName AddressID AddressTable AddressID City Street ...

SQL Server 2008 Geospatial queries

SQL Server 2008 has new geo data types. One thing I am wondering though is that if I have a table with geocoded addresses in it, can I write a query to display all records within a certain distance. For example, many websites have the option to search for items within a certain distance such as 50 miles and would like it if SQL 2008 prov...

How to query excel file in C# using a detailed query

Hi - The following code returns data from a spreadsheet into a grid perfectly [ string excelConnectString = "Provider = Microsoft.Jet.OLEDB.4.0;" + "Data Source = " + excelFileName + ";" + "Extended Properties = Excel 8.0;"; OleDbConnection objConn = new OleDbConnection(excelConnectStr...

sql statement to delete records older than XXX as long as there are more than YY rows

Assume a table with the following columns: pri_id, item_id, comment, date What I want to have is a SQL query that will delete any records, for a specific item_id that are older than a given date, BUT only as long as there are more than 15 rows for that item_id. This will be used to purge out comment records older than 1 year for the i...

SQL Query - Using Order By in UNION

How can one programmatically sort a union query when pulling data from two tables? For example, SELECT table1.field1 FROM table1 ORDER BY table1.field1 UNION SELECT table2.field1 FROM table2 ORDER BY table2.field1 Throws an exception Note: this is be attempted on MS Access Jet database engine ...

N prefix before string in Transact-SQL query

Would you tell me, please, when should I use N prefix before string in Transact-SQL query? I have started to work with a database where I don't get any results using query like this SELECT * FROM a_table WHERE a_field LIKE '%а_pattern%' until I change pattern to N'%а_pattern%'. I never had to add this prefix in the past, so I am curio...

Corel draw 13 mysql query

Is there a way to do a mysql database query in Corel Draw 13(X3)? ...

MySQL correlated subquery

Having difficulty articulating this correlated subquery. I have two tables fictitious tables, foo and bar. foo has two fields of foo_id and total_count. bar has two fields, seconds and id. I need to aggregate the seconds in bar for each individual id and update the total_count in foo. id is a foreign key in bar for foo_id. I've tried ...

Oracle database role - select from table across schemas without schema identifier

Which Oracle database role will allow a user to select from a table in another schema without specifying the schema identifier? i.e., as user A- Grant select on A.table to user B; B can then- "Select * from table" without specifying the 'A'. One of our databases allows this, the other returns a 'table or view does not exist' error. ...

Access - What's wrong with this query...

Hi to all, I'm trying to get a query working that takes the values (sometimes just the first part of a string) from a form control. The problem I have is that it only returns records when the full string is typed in. i.e. in the surname box, I should be able to type gr, and it brings up green grey graham but at present it's not bring...

SQL: How do I SELECT only the rows with a unique value on certain column?

Thanks a million everyone for everyone's response. Unfortunately, none of the solutions appear to be working on my end, and my guess is that the example I've provided is messed up. So let me try again. My table looks like this: contract project activity row1 1000 8000 10 row2 1000 8000 20 row3 1000 8001 10 row4 2000 9000 49 row5 200...

How do I query if a database schema exists

As part of our build process we run a database update script as we deploy code to 4 different environments (due to crazy business rules, but that's another post). Further, since the same query will get added to until we drop a release into production (yet a few more domains to add to... frustrating); it HAS to be able to run multiple ti...

Semi-Tricky SQL Query

I am trying to write a query for SQL Server 2005 but I can't figure out how to do it. I have a table with the following fields: MessageID int CategoryID int Priority tinyint MessageText NVARCHAR(MAX) I need a query that will return * for each row that has the highest priority within a Category. For example, if I had the following dat...

How to run a very large query (SQL Server and ColdFusion)

I've got a reasonably simple query (this time) that I need ALL the results back from (I'm storing them in an excel spreadsheet). The query itself times out the server, so how do I go about running it without that happening? ...

SQL stored procedure temporary table memory problem

We have the following simple Stored Procedure that runs as an overnight SQL server agent job. Usually it runs in 20 minutes, but recently the MatchEvent and MatchResult tables have grown to over 9 million rows each. This has resulted in the store procedure taking over 2 hours to run, with all 8GB of memory on our SQL box being used up. T...

In What Order are MySQL JOINs Evaluated

I have the following query: SELECT c.* FROM companies AS c JOIN users AS u USING(companyid) JOIN jobs AS j USING(userid) JOIN useraccounts AS us USING(userid) WHERE j.jobid = 123; I have the following questions: Is the USING syntax synonymous with ON syntax? Are these joins evaluated left to right? In other words, does this query sa...

Avoiding SQL Injection in SQL query with Like Operator using parameters?

Taking over some code from my predecessor and I found a query that uses the Like operator: SELECT * FROM suppliers WHERE supplier_name like '%'+name+%'; Trying to avoid SQL Injection problem and parameterize this but I am not quite sure how this would be accomplished. Any suggestions ? note, I need a solution for classic ADO.NET - I d...

Copying rows in MySQL

I want to copy all of the columns of a row, but not have to specify every column. I am aware of the syntax at http://dev.mysql.com/doc/refman/5.1/en/insert-select.html but I see no way to ignore a column. For my example, I am trying to copy all the columns of a row to a new row, except for the primary key. Is there a way to do that wit...

Way to preview (retroview) SQL queries of SQL Server Management Studio Express

Could you tell me, please, if it's possible to preview (or at least retroview, for example, in a kind of a log file) SQL commands which SQL Server Management Studio Express is about to execute (or has just executed)? In the past I used Embarcadero DBArtisan which shows SQL queries to be executed before actually running them on the serve...