sql

Sql server top query

I am experiencing some strange behaviour and I really don't have test date/ environments, so Will this query return top 5 rows ordered by count, or will it take top 5 rows and sort them then. What would be the query for the first? select top 5 l.userId, count(*) "count" from Log l where ... group by l.userId order by "count" des...

Sql syntax error

Here is my query: $query="Delete b Where Exists ( Select 1 From a Where a.poster_password = '$pass' And a.ad_id = '$id' And a.classified_id = b.classified_id ) Delete a Where a.poster_password = '$pass' And a.ad_id = '$id'"; I get this error: "You have an error in your SQL syntax; chec...

Inner case to main where select :-/

From answers : ALTER PROCEDURE [dbo].[HardQ3] (@ID_User nvarchar(15), @ID_ListGroupParIzm Integer,@CIzmer Integer, @CPoint Integer, @UserExt bit) AS SELECT * FROM ( SELECT C.ID_ListParIzm, C.Hint, CAST(CASE WHEN T2.ID_Param IS NULL AND T2.UserExt=@UserExt THEN 1 ELSE 0 END as bit) AS V...

Why is my SQL 'NOT IN' clause producing different results from 'NOT EXISTS'

I have two SQL queries producing different results when I would expect them to produce the same result. I am trying to find the number of events that do not have a corresponding location. All locations have an event but events can also link to non-location records. The following query produces a count of 16244, the correct value. SELEC...

select a value where it doesnt exist in another table

I have two tables Table A: ID 1 2 3 4 Table B: ID 1 2 3 I have two requests: I want to select all rows in table A that table B doesn't have, which in this case is row 4. I want to delete all rows that table B doesn't have. I am using SQL Server 2000. ...

MySql syntax error; Deleting from two tables in one query possible?

Is it not possible to perform two deletes in one query by separating them with a semicolon? Here is my query: $query="DELETE FROM $sql_table WHERE EXISTS ( SELECT 1 FROM classified WHERE classified.poster_password = '$pass' AND classified.ad_id = '$id' AND classified.classified_id = $sql_table.classified_id ...

move data from one table to another, postgresql edition

Hi All, I'd like to move some data from one table to another (with a possibly different schema). Straightforward solution that comes into mind is - start a transaction with serializable isolation level; INSERT INTO dest_table SELECT data FROM orig_table,other-tables WHERE <condition>; DELETE FROM orig_table USING other-tables WHERE <co...

MySQL - Dynamic SQL inside stored procedure

Hi, see the procedure InsertHelpQuestion2 below. DROP PROCEDURE InsertHelpQuestion2; CREATE PROCEDURE InsertHelpQuestion2(user_id char(50),scenerio_id char(50),component_id char(50)) BEGIN DECLARE uu_id char(50); SELECT BinToUUID(NewUUID()) INTO uu_id from helpquestion LIMIT 1; SET @fields_part = 'INSERT INTO helpquestion(H...

SQL Query to select count = 0

OK, I need a T-SQL guru for this one. I have a table called PackageStoreEvents that has a packageid, storecode and an eventcode field. I want to return the packageid values for storecode 2406 where the count for the eventcode = 6 is 0. How would I do that? I'm stuck on this one. Thanks for the help ...

Permissions Problems Prevent Database Access

Hi. Recently I inherited a database from another programmer (created / stored with SQL Management Studio 2008), and I am having massive difficulties accessing, modifying, or even viewing the previously created databases. Specifically, when I try to load one of the databases in SQL Management Studio I get the following error message: ...

MySQL: Getting results present in one time frame but not in another time frame.

I have generated a dataset that contains data spanning thirty days. Im trying to issolate new data elements that have appeared in the last 2 days but not in the previous 28 days before that. I run a PHP script that generates the test data. (PHP and MYSQL return the same time when tested) I run the following query against it. Results a...

Different results on the basis of different foreign key value using a falg in where clause

Please see attached image. Can you please tell me what query will work. Please ignore isdefinite and productpriceid columns. Thanks ...

Simple mysql query (i think) - select multiple rows into 1 field

I have two tables, Profiles and ProfileInformation. Any profile may have multiple entries in ProfileInformation. I am trying to create a query that takes all of the profile information for a specific profile and put it in to a single field, for example: Profile[0] has 3 corresponding records in ProfileInformation, lets say phone numbe...

How effectively manage a database: using direct sql commands or DataSets?

Sometimes I need to develop a simple database application using MS Access Databases. I`ve learned that I can manipulate data in my tables using direct SQL Commands or gather everything in a DataSet and update any changes that it tracks (insert, delete...). But the question is: should my app make changes is my database tables using dire...

Using Distinct or Not

In the below SQL Statement, should I be using DISTINCT as I have a Group By in my Where Clause? Thoughts? SELECT [OrderUser].OrderUserId, ISNULL(SUM(total.FileSize), 0), ISNULL(SUM(total.CompressedFileSize), 0) FROM ( SELECT DISTINCT ProductSize.OrderUserId, ProductSize.FileInfoId, CAST(ProductSize.FileSize AS BIGINT) AS FileSize, ...

Databases and B+Trees indexes

Where can I find information on whether which databases are using B+Trees over B-Trees for their index implementations? Oracle appears to be using B+Trees. Although they don't describe it on their documentation, their graphics appear to be pointing that B+Trees are actually being used. ...

Can You Import an Excel File to a MySQL Database using phpMyAdmin?

Can You Import an Excel File to a MySQL Database using phpMyAdmin? I am looking to buy this database that has the data of all Colleges and Universities in the US. The file is in Excel format. Can this be imported into phpMyAdmin? Here is the site where I am going to buy the database from if this is possible: http://www.data-lists.com/u...

DataSet.GetChanges - Save the updated record in a different table than the source one

I'm doing operation on a dataset containing data from a sql table named Test_1 and then get the updated records using the DataSet.GetChanges(DataRowState.Modified) function. Then i try to save the dataset containing the updated records on a different table than the source one (the table is names Test and has the same structure as Test_1)...

LINQ-to-SQL with SQLite: syntax error near "SELECT" when inserting

I have been using the System.Data.SQLite provider for my application. When I try to create a new object and insert it into the database, however, I get a SQL syntax error near "SELECT". Basically my code looks like: //supplies is a DataContext connected to my DB Table<Store> stores = supplies.Stores; //... Store newStore = new Store();...

Selecting a range with two fields

I have a table with a job # and a suffix. The query i'm trying to create selects a range of job # / suffix entries where the suffix is dependant upon the job #. So for example: Job # Suffix -------- ----------- 00000001 001 00000001 002 00000001 003 00000002 001 00000002 ...