sql

Perform single selection with where

I'd like to achieve the following SQL statement with subsonic 2.2 SELECT Product.* FROM Product WHERE Product.OurPrice <> Product.RetailPrice The Subsonic select query I've started with: SubSonic.SqlQuery select = new SubSonic.Select() .From<Product>() .Where(Product.Columns.OurPrice) .IsNotEqualTo(... object /*Should be Product.Co...

SQL Server Version Updating Tables

I am part of a software development company looking for a good way to update my SQL Server tables when I put out a new version of the software. I know the answer is to probably use scripts in one form or another. I am considering writing my own .NET program that runs the scripts to make it a bit easier and more user-friendly. I was wond...

MS Access - Select Char as Date and doing a date diff

I have two columns. ColA and ColB contains char(10) with data "20090520" and "20090521". I want to select and get the date difference in days. I have tried using Format() and CDate() but MS Access always display as #ERROR. ...

Getting list of cities and countries from a SQL Table

I have a SQL table where in each row I store the country and the city of a things location. For example: record1, New York, USA record2, Rome, Italy record3, Milano, Italy record3, Birghiman, UK record4, London, UK record5, London, UK record6, London, UK record7, Birmingham, UK I would like to generate a list that is ordered by cou...

Store and retrieve a multidimensional array using php and mysql

I have a multidimensional array in PHP like this: $array = array( "Part1" => array( "Subpart1" => array(0, 1), "Subpart2" => array(1, 0) ), "Part2" => array(0), "Part3" => array(0, 1, 0) ); Now I want to store this array in a MySQL table and retrieve it exactly like this again on another PHP page. I've...

Fastest way to check date range...

I store events in SQLServer 2005 where the time the event occured is important and must be stored in the datebase. What is the fastest way to write the date range check in the where clause to ensure everything on that day is selected? Currently when @DateStart and @DateEnd are passed in I set @DateStart to midnight and set @DateEnd to t...

Queries in ms-access:formatting a field in the middle of an sql UPDATE code.

Hello again, I am having yet another problem with my data in ms-access. Basically, what i'm doing is using multiple sql statements to pull, sort, and staight up change/manipulate data. The problem that im having currently is that i am trying to use a sql code like this: UPDATE analyzedCopy2 SET analyzedCopy2.DateRange = #4/21/2009# to ...

Using Avg() with update

I have a table with multiple readings_miu_id's each with multiple RSSI readings (RSSI is also the name of the field). So, i currently have a datasheet, with many columns, but the two pertinent ones to this conversation look something like this: readings_miu_id RSSI =============== ==== 11011032 -90 11011032 -81 110110...

MySQL: Determine Table's Primary Key Dynamically

I'm, generating a SQL query like this in PHP: $sql = sprintf("UPDATE %s SET %s = %s WHERE %s = %s", ...); Since almost every part of this query is dynamic I need a way to determine the table's primary key dynamically, so that I'd have a query like this: $sql = sprintf("UPDATE %s SET %s=%s WHERE PRIMARY_KEY = %s", ...); Is there a M...

SQL server string manipulation in a view... Or in XSLT

I have been passed a piece of work that I can either do in my application or perhaps in SQL: I have to get a date out of a string that may look like this: 1234567-DSP-01/01-VER-01/01 or like this: 1234567-VER-01/01-DSP-01/01 but may look like this: 00 12345 DISCH 01/01-VER-01/01 XXX X XXXXX Yay. if it is a "DSP" then I want that ...

MySQL 5 doesn't recognize BEFORE?

In MySQL 5.0.51b on my Mac, ordinals beyond FIRST fail, as does BEFORE. So, ALTER TABLE my_contacts ADD COLUMN phone VARCHAR(10) FOURTH; fails altogether, as would ALTER TABLE my_contacts ADD COLUMN phone VARCHAR(10) BEFORE email; Do these work with any other flavors or versions of MySQL? ...

SQL query to avoid fetching the entire nested set when parts of it are collapsed by the user

I'm trying to tie django-mptt and contrib.admin together by providing something friendlier than a flat list in the admin. Because the trees are supposed to be large (otherwise i wouldn't be using nested sets), users should be able to expand and collapse parts of it. When a user expands or collapses or expands a branch (ajax is used for...

Insight on a query optimization

I am trying here to basically find users that do have sports & regions targeted by an activity. In the acces [users] table there is around 17K users. Each can have a certain number of sport interests and one region. There query here look for each users that have one sport & one region at least that are targeted via the activities. Spor...

SQL query works on testing server but not live... what could be the difference?

SOLVED: I wrote and tested a PHP script on the local server. (Nothing fancy, just 2 consecutive SQL inserts in the same database, but different tables). Both servers run PHP5 & MYSQL 5. On the local server, both queries are processed correctly. On the live server, only the first query works, but not the second and I can't figure out ...

Matching rows without filtering (SQL)

I've got table with properties of some products: table properties( prop_id int, product_id int ) My question is: is it possible to select properties that match without filtering them from the result? For example: select property_id from properties WHERE property_id IN(1,3,5); You get only rows that match 1,3,5. I need all rows...

SQL left join vs multiple tables on FROM line?

Duplicate of: What’s the difference between just using multiple froms and joins? Most SQL dialects accept both the following queries: SELECT a.foo, b.foo FROM a, b WHERE a.x = b.x SELECT a.foo, b.foo FROM a LEFT JOIN b ON a.x = b.x Now obviously when you need an outer join, the second syntax is required. But when doing an inner joi...

Is there any reason to worry about the column order in a table?

I know you can ALTER the column order in MySQL with FIRST and AFTER, but why would you want to bother? Since good queries explicitly name columns when inserting data, is there really any reason to care what order your columns are in in the table? ...

How can we migrate to using VS2005's Database Projects?

At my company, our current method of updating the database is to connect using the Server Explorer in VS2005, then modify the stored procedures by opening them and editing. The devs here seem to enjoy that "write and save it like it's code" mentality. It is pretty convenient, how it automatically turns Create into Alter and runs the sc...

MySQL to get the count of rows that fall on a date for each day of a month

I have a table that contains a list of community events with columns for the days the event starts and ends. If the end date is 0 then the event occurs only on the start day. I have a query that returns the number of events happening on any given day: SELECT COUNT(*) FROM p_community e WHERE (TO_DAYS(e.date_ends)=0 AND DATE(e.dat...

SQL Many-to-Many Query Problem

I have three tables: videos, videos_categories, and categories. The tables look like this: videos: video_id, title, etc... videos_categories: video_id, category_id categories: category_id, name, etc... In my app, I allow a user to multiselect categories. When they do so, I need to return all videos that are in every selected categor...