sql

SQL Overlapping and Multi-Column Indexes

I am attempting to tune some stored procedures and have a question on indexes. I have used the tuning advisor and they recommended two indexes, both for the same table. The issue is one index is for one column and the other is for multiple columns, of which it includes the same column from the first. My question is why and what is the di...

Multiple Table Joins to Improve Performance?

If I have a table structure like this: Transaction [TransID, ...] Document [DocID, TransID, ...] Signer [SignerID, ...] Signature [SigID, DocID, SignerID, ...] And the business logic is like this: Transactions can have multiple documents Documents can have multiple signatures And the same signer can have multiple signatures in mu...

MySQL table data transformation -- how can I dis-aggregate MySQL time data?

We are coding for a MySQL data warehousing application that stores descriptive data (User ID, Work ID, Machine ID, Start and End Time columns in the first table below) associated with time and production quantity data (Output and Time columns in the first table below) upon which aggregate (SUM, COUNT, AVG) functions are applied. We now ...

Scheduled export of database structure (table, view, sp) to file

I'm using SQL 2005. I can right click on a database and create scripts for the database that will recreate the structure (tables, views, stored procedures) elsewhere. Or just as a backup, version, etc. But, is there a way I can schedule it to do this? And output to a folder I choose? I really appreciate the help. Don ...

MS Access 2003 - Unbound Form uses INSERT statement to save to table; what about subforms?

So I have an unbound form that I use to save data to a table on button click. Is there a way I can have subforms for entry that will allow me to save data to the table within that same button click? Basically I want to add more entry options for the user, and while I know other ways to do it, I am particularly curious about doing it thi...

MySQL query returns different set of results on two identical databases

I exported a live MySQL database (running mysql 5.0.45) to a local copy (running mysql 5.1.33) with no errors upon import. There is a view in the database, that when executed locally, returns a different set of data than when executed remotely. It's returning 32 results instead of 63. When I execute the raw sql, the same problem occurs. ...

MDX Year on Year Sales by Months

Hi all, I'm stuck on a MDX query, I'm trying to retrieve the following results: [Time].[2009] [Time].[2010] [Time].[Months].Members [Measures].[Sales] [Measures].[Sales] So I would like to compare the sales which were in 2009 against 2010 by month. In terms of a chart I would have two series one fo...

Export products and variants from SQL Server

I have a SQL Server DB that has a table of products, and another table which contains a list of the sku variants of each product if it has one. I want to export all the products and their SKU's into excel. At the moment, I have a helper SQL function which performs the subquery against a product_id and concatenates all the SKU's into a c...

MySQL, SQL Select Statement, Where with OR... What's wrong with this?

I'm looking for help with my query below. which is never returning anything for veggie... Is the way I have my WHERE statement written valid? SELECT * FROM newsfeed INNER JOIN newsfeedaction ON newsfeed.newsfeedactionid = newsfeedaction.newsFeedActionID INNER JOIN person ON newsfeed.personID = person.personID LEFT OUTER JOIN food ON...

.NET datetime issue with SQL stored procedure

I am getting the below error when executing my application on a Windows XP machine with .NET 2.0 installed. On my computer Windows 7 .NET 2.0 - 3.5 I am not having any issues. The target SQL server version is 2005. This error started occurring when I added the datetime to the stored procedure. I have been reading alot about using .NET da...

How concurrency mantained by Oracle for PL/SQL Procedure?

How oracle maintained concurrency of PL/SQL Program? i.e. If user A and B both execute same procedure (Contains DML operations) @ same time. ...

mysql group by query

I have the following table: Service_ID feedback 31 1 32 1 33 1 I have the following query to find the sum: SELECT Service_ID,SUM(consumer_feedback) FROM consumer5 group by Service_ID I have to get the output as 31 1 32 1 33 1 I am getting it. But for my project I have 34, 35 also. I shou...

When calling CRUD check if "parent" exists with read or join?

All my entities can not be deleted - only deactivated, so they don't appear in any read methods (SELECT ... WHERE active=TRUE). Now I have some 1:M tables on this entities on which all CRUD operations can be executed. What is more efficient or has better performance? My first solution: To add to all CRUD operations: UPDATE ... JOIN ...

"SELECT TOP", "LEFT OUTER JOIN", "ORDER BY" gives extra rows

I have the following Access 2002 query I'm running through OLE DB in .NET: SELECT TOP 25 tblClient.ClientCode, tblRegion.Region FROM (tblClient LEFT OUTER JOIN tblRegion ON tblClient.RegionCode = tblRegion.RegionCode) ORDER BY tblRegion.Region There are 431 records within tblClient that have RegionCod...

Storing a pre-processed varchar column in the database along with the original one

Hi all, I have a big table with names and surnames. People search this database via a Web interface. PHP code queries the table with LOWER(@name) = LOWER(name). In order to make the search faster, I want to make a derived column in the table named lower_name and always LOWER() the names before storing. But when it comes to send the resu...

possible to create sql query with table wildcards?

this is probably a simple question, but i have been unable to find a solution online, any help would be much appreciated. I'm trying to create an SQL query in PHP and would like to somehow apply a wild card to the TABLE filter... something perhaps like.... select * from %table%. However, I have only so far been able to see filters for ...

Optimizing MySQL queries with IN operator

I have a MySQL database with a fairly large table where the products are. Each of them has its own id and categoryId field where there is a category id belongs to this product. Now I have a query that pulls out products from given categories such as: SELECT * FROM products WHERE categoryId IN ( 1, 2, 3, 4, 5, 34, 6, 7, 8, 9, 10, 11, 12 ...

T-SQL Question : Query to XML

anyone can show me how to generate from this data ------------------------DATA-------------------------- Key ParentKey 5 NULL 25 5 33 25 26 5 27 5 34 27 28 5 29 5 to this XML result? ---------------------RESULTS-------------------------- <record key="5" parentkey = ""> <record key="25" parentkey = "5"> <record key="3...

LINQ-to-SQL: Searching against a CSV

I'm using LINQtoSQL and I want to return a list of matching records for a CSV contains a list of IDs to match. The following code is my starting point, having turned a CSV string in a string array, then into a generic list (which I thought LINQ would like) - but it doesn't: Error Error 22 Operator '==' cannot be applied to operands of ...

How do I set order priority in full text search (Sql Server)

I have simple tbl_posts table with three columns (id, name, content) I created fulltext index for name and content columns. When I query it like: SELECT * FROM dbo.tbl_posts WHERE FREETEXT ( *, 'search word' ) I want that the order for results will be ordered first by rank in column name and then my content ...