sql

Conditional composite key in MySQL?

Hi So I have this table with a composite key, basically 'userID'-'data' must be unique (see my other question http://stackoverflow.com/questions/2026379/sql-table-semi-unique-row) However, I was wondering if it was possible to make this only come into effect when userID is not zero? By that I mean, 'userID'-'data' must be unique for no...

In mysql how do I find rows repeating with respect to all fields ?

+------------+------------+ | student | department | +------------+------------+ | 1234567890 | CS | | 1234567890 | ME | | 1234567890 | CS | | 000000001 | ME | +------------+------------+ How can I get rows that are repeating with respect to both fields? Thanks in advance. ...

Cast xml to string in LINQ query

Is there a way to cast an xml to a string in a Linq query. Something like this, but in LINQ: select TOP(10) * from PackageSessionNodes where CAST(Interactions as nvarchar(max)) like '%asset id%' order by PackageSessionNodeId desc This doesn't work: var packageSessionNodes = from psn in db.PackageSessionNodes ...

Run a SQL query on existing recordset?

I've created a 3rd recordset (disconnected) from two existing recordsets that came from different connections. Now, I'd like to run a SQL query on this 3rd recordset. I am using Excel VBA. thanks, Harry ...

Oracle - Avoiding invalidation errors

I've noticed that whenever I have package-level constants (or any variables for that matter), whenever I recompile the package, persistent connections to the database get an error that "existing state of package body has been invalidated". Is there a way to avoid this? Perhaps through synonyms? What are best-practices in this case? ...

MS-Access Get price of product at certain order date

I have a table filled with purchase prices, like this: sku price btw startdate PCR-CA5425023181515 21,17 € 1 01/01/2009 PCR-CA5425023181515 999,00 € 1 06/06/2009 PCR-CA5425023181515 444,00 € 4 09/07/2009 PCR-CA5425023181515 100,00 € 4 10/08/2009 I have another table filled with orders, ...

SQL Server 2005 Full Text Search over multiple tables and columns

Hi, I'm looking for a good solution to use the containstable feature of the SQL Serve r2005 effectivly. Currently I have, e.g. an Employee and an Address table. -Employee Id Name -Address Id Street City EmployeeId Now the user can enter search terms in only one textbox and I want this terms to be split and search with an "AND" opera...

Database design: why use an autoincremental field as primary key?

Hi, here is my question: why should I use autoincremental fields as primary keys on my tables instead of something like UUID values? What are the main advantages of one over another? What are the problems and strengths of them? ...

Writing a custom log provider in SSIS

Hi everybody, I'm currently working with Microsoft SSIS and SQL server 2008 and I would like to write logs to a file using : Specific log pattern : Formatted Date - Log Level : Log Message Specific log levels Error Codes that are associated to error messages in a configuration file I was wandering on the Web looking for a solution...

POS Database Layout

Our current database layout for store inventory includes a table that houses a record for each item and includes the item attributes such as price, cost, description, SKU, etc. Each store then has a table that has the SKU and the Quantity, and a few other attributes that are different for each store depending on the SKU. Although this ...

Update query with 'not exists' check causes primary key violation

The following tables are involved: Table Product: product_id merged_product_id product_name Table Company_Product: product_id company_id (Company_Product has a primary key on both the product_id and company_id columns) I now want to run an update on Company_Product to set the product_id column to a merged_ product_id. This update co...

MySQL - Selecting "Everyone who booked last January, but hasn't booked this January".

I am having a few issues with what is probably a reasonably simple SQL statement - unfortunately, my skills don't go as far as this and I can't get my head round it! I have, for example, the following tables: booking_record client Booking record may have many bookings for one client, they are linked via 'labelno'. I would like to s...

Negative integer indexes: are they evil?

I have this database that I'm designing. It needs to contain a couple dozen tables with records that we provide (a bunch of defaults) as well as records that the user can add. In order to keep the user from shooting himself in the foot, it's necessary to keep him from modifying the default records. There are lots of ways to facilitate ...

Magento Store - SQL Error

I get this error in my Magento store when I turn on flat catalogs: SELECT COUNT(_table_views.event_id) AS `views`, `e`.*, `e`.`entity_id`, `e`.`attribute_set_id`, `e`.`type_id`, `e`.`category_ids`, `e`.`created_at`, `e`.`enable_googlecheckout`, `e`.`has_options`, `e`.`image_label`, `e`.`links_purchased_separately`, `e`.`links_title`, `e...

In mysql How can I multiply two tables?

Basically what I want is cross product of two tables. t1 is : +------------+ | student | +------------+ | 1234567890 | | 1234567890 | | 1234567890 | | 000000001 | +------------+ t2 is: +--------+ | number | +--------+ | 1 | | 3 | +--------+ How can I get a tab...

How to nest select statements in SQL?

I'm trying to select only the User_IDs that are in the first select statement and the last select statement, but I am doing something wrong. SELECT COUNT(DISTINCT User_ID) AS total FROM UserClicks WHERE (Date > :startDate AND Date < :endDate) AND User_ID IN ( SELECT User_ID FROM UserClicks WHERE (Date > :mo...

Howto call views inside another SELECT statement (SQL Server)?

I created a view. The view's Query works fine. Now I want to write another Query which uses the first one. As far as I know it should look like this: SELECT * FROM myView; Now there is an Error returned: "Ungültiger Objektname 'myView'" in English its something like "unvalid object name 'myView'. How do I refer to or call views the r...

How to optimize this SQL select query?

I am able to complete this query but it takes 25 seconds. That's too long! How can I optimize this query? SELECT COUNT(DISTINCT u1.User_ID ) AS total FROM UserClicks u1 INNER JOIN (SELECT DISTINCT User_ID FROM UserClicks WHERE (Date BETWEEN DATE_SUB(:startDate, INTERVAL 1 MONTH) AND :startDate)) u2 ...

After doing a SQLAlchemy update(), is there a way to get the changed values?

After calling update(), I know that the return value has a .rowcount attribute that will reveal how many rows were changed. Is there a way to get the actual new values that they were changed to? For example, if I do the SQLAlchemy equivalent of: UPDATE t SET x=x+1 WHERE y=z ...is there a way to get the new value for x? Or do I have t...

SQL example: retrieve musicianID with no gigs played?

Suppose you have two tables: Musicians musicianID (primary key, int) name (varchar) instrument (varchar) Gigs GigID (primary key, int) musicianID (foreign key from Musicians) location (varchar) How would I retrieve the musicianIDs which have not played a gig i.e. that don't have a reference in the Gigs table...