sql

SQL query execution - different outcomes on Windows and Linux

The following is generated query from Hibernate (except I replaced the list of fields with *): select * from resource resource0_, resourceOrganization resourceor1_ where resource0_.active=1 and resource0_.published=1 and ( resource0_.resourcePublic=1 or resourceor1_.resource_id=resource0_.id ...

MySQL XA Transaction in two different connections, rollback of one doesnt rollback all

Hi all! maybe I totally misunderstand XA Transactions (I'm no DBA), but I have the following problem: - I open two sql connections (to the same DB) - "set autocommit=0" in both sessions - XA start '1234','56'; in one session - XA start '1234','57'; in other session - insert something in both sessions - "xa end", "xa prepare" and "xa com...

Delete multiple duplicate rows in table

I'm sure this has been asked before, but I was having a hard time finding it. I have multiple groups of duplicates in one table (3 records for one, 2 for another, etc) - multiple rows where more than 1 exists. Below is what I came up with to delete them, but I have to run the script for however many duplicates there are: set rowcount ...

Grouping multiple counts with each row representing one ID

Hi, I have a table that logs activity. It has an int activity column that indicates what type of activity it was and a corresponding admin_id that indicates which admin performed the action. Each row in the table represents one action, so for example, an administrator would cancel a user's account, and a row with that admin's id and acti...

MySQL: How to select and display ALL rows from one table, and calculate the sum of a where clause on another table?

I'm trying to display all rows from one table and also SUM/AVG the results in one column, which is the result of a where clause. That probably doesn't make much sense, so let me explain. I need to display a report of all employees... SELECT Employees.Name, Employees.Extension FROM Employees; -------------- | Name | Ext | ------------...

Good database for large table with simple key access

I have a few large databases, greater than 100 million records. They consist of the following: A unique key. An integer value, not unique, but used for sorting the query. A VARCHAR(200). I have them in a mysql isam table now. My thought was, hey, I'll just set up a covering index on the data, and it should pull out reasonably fast....

SQL - why would this not work?

The following script allows you to create the database and populate it with necessary data for my question: # HeidiSQL Dump # # -------------------------------------------------------- # Host: 127.0.0.1 # Database: blueskylearning # Server version: 5.1.22-rc-community # Server OS: Win32 # Ta...

View XML data as a table

Is there a way I can use Visual Studio to view my XML data in a tabular structure by applying the appropriate XSD to it? Are there any other tools I can easily use for this purpose? PS: I need this for a one time use. ...

Returning values in two rows that aren't equal

I'm trying to return the results of a query where two columns columns are not equal to each other. However, when I run the query the results don't seem to be consistent with the actual data. As you can see below, I want to return rows where the RatePlanIDs are different. RatePlans_Supplemental.PIDs could actually be null, but I n...

how to count multiple fields in sql

Hey I've found a lot of people asking questions similar to mine but not exactly the same. I have a database that stores IPs. There is a server field and a client field. I want to look through both these fields and pull out all the distinct IPs for them and then find out how many occurrences there are of each IP as a client and as a se...

SQL Query - Ensure a row exists for each value in ()

Currently struggling with finding a way to validate 2 tables (efficiently lots of rows for Table A) I have two tables Table A ID A B C Table matched ID Number A 1 A 2 A 9 B 1 B 9 C 2 I am trying to write a SQL Server query that basically checks to make sure for every value in Table A there exists a row for a v...

cakephp SQL error at $this->User->saveField()

Hey guys. When I try to update a user, let's say his account balance $this->User->id = $validUserId; $this->User->saveField('balance', 100); I get this error SQL Error: 1054: Unknown column 'User.group_id' in 'field list' with this automatically generated query druring the save process SELECT `User`.`group_id` FROM `users` AS `Us...

What is the difference between using a cross join and putting a comma between the two tables?

What is the difference between select * from A, B and select * from A cross join B ? They seem to return the same results. Is the second version preferred over the first? Is the first version completely syntactically wrong? ...

Correct way of processing user input strings in SQL and PHP ?

I have designed a comment system. This is what I am basically doing. $story=$_POST['story']; $story=mysql_real_escape_string($story); $query = "INSERT INTO `comment` VALUES('$story')"; Now the problem is when i store the comment all the " are replaced by \" and all the ' are replaced by \'. So when I display the comments back these \ ...

Is there a way to have a SQL statement return a string value from a map that has an integer as the key?

For example: In the database we have number representations of fields that correspond to strings in the app. We store the data as an integer for size and speed reasons. During testing we compare what's in the app with SQL statement results. The problem is that we see the numbers in the results but there are strings in the app. There is ...

Bookmark Lookups - How efficient is the lookup using a row pointer?

I'll use this simple query as an example: Select MyColumn From MyTable Where PrimaryKeyColumn = 10. Let's assume the table has no clustered index and there is a primary key on PrimaryKeyColumn. If I understand correctly, the query plan will include an index seek on the primary key and a bookmark lookup on the table, using some sort of...

Sql Server 2000 openrowset issue.

I’m attempting to query and Excel spread sheet on a network share, using the openrowset function with no success. I get the following error: SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;DATABASE=\\server\Xdrive:\Spreadsheet.xls', 'Select * from [Sheet1$]') Server: Msg 7399, Level 16, State 1, Line 1 O...

Database sorting SQL Server

Hi folks! I made a windows app and I need to add a "sort" by selected column. The grid has 4 columns and lot of records (paged of course). The question is: Should I consider to add an index for every column that need sorting in the grid? thanks in advances. Regards ...

SQL error #1064

Trying to enter a value into a mediumblob field in a mySQL table but I'm getting: 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE id = 40' at line 3 INSERT INTO 'Picture' VALUES (efvr) WHERE id = 40 What's wrong with this statem...

Hiding/disabling ASP.NET controls when SqlDataSource is empty

Hi, I need to be able to hide or disable a multitude of items (detailsviews, gridviews) when an SqlDataSource returns no rows. So if the page is reposted and no rows are selected, all the controls would be disabled. How would I do this? Thanks ...