sql

why cant I use a temp column in the where clause?

for example this query: Select product_brand, (CASE WHEN COUNT(product_brand)>50 THEN 1 ELSE 0 END) AS brand_count FROM products WHERE 1 GROUP BY product_brand This brings up 2 columns one called product_brand and one called brand_count, brand_count is created on the fly and is always 1 or 0 depending on wh...

MSSQL - Split a field into 3 fields

I have a resultset consisting of 1 column and in this case 2 rows the single column [ProductDescription] is a varchar field that hold 3 pieces of information (I didn't design it) i need to get those three pieces of information out into 3 additional fields using a query before /------------------------------\ |ProductDescription ...

Should I use the sql JOIN keyword for complex joins on multiple tables ?

I've got the following request : select * from tbA A, tbB B, tbC C, tbD D where A.ID=B.ID and B.ID2= C.ID2 and A.ID=D.ID and C.ID3=D.ID3 and B.ID4=D.ID4 and A.Foo='Foo' I've heard several times that this join syntax is depreciated, and that I should use the 'JOIN' keyword instead. How do I do that in such a complicated ...

retrieving max records from sqlserver

I have been toiling with this for a while now. so decided to ask and get some help. I have two tables as such: tblTrans: (DocNumber field is always unique) DocNumber TransDate userId 66-FF-GHIP 03-05-08 someUser 55-RT-JHTP 03-09-09 someOtherUser 77-AF-KPWT 05-08-09 userId1 09-IO-TEAG 0...

Get Latest Entry from Database

How can I get the latest entry by the latest DATE field from a MySQL database using PHP? The rows will not be in order of date, so I can't just take the first or last row. ...

Sql error while running query data type mismatch in criteria expression

I'm getting the following error while running the SQL query given below 'Warning: odbc_exec() [function.odbc-exec]: SQL error: [Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression., SQL state 22005?T in SQLExecDirect in D:\xampp\htdocs\fypphp\functions.php on line 543' INSERT INTO vehicl...

Rollup Column for Normalized Sums (SQL) - Part 1

I have a table like so: object_id | vote 1 | 2 1 | -1 1 | 5 2 | 3 2 | 1 3 | 4 3 | -2 I want this result (for this particular example, object_ids 1 and 2 are part of a group, defined elsewhere, and I'm looking for the normalized_score so that the sum always = 1. object_id 3 is part of an unused group.): object_id | normalized_score 1...

SQL Constraints Question

In Sql Server 2005, I have a table with two integer columns, call them Id1 and Id2. I need them to be unique with in the table (easy enough with a unique index that spans both columns). I also need them to be unique in the table if the values are transposed between the two columns. For example, SELECT * FROM MyTable returns Id1 Id2 ...

Ideas for Combining Thousand Databases into One Database

We have a SQL server that has a database for each client, and we have hundreds of clients. So imagine the following: database001, database002, database003, ..., database999. We want to combine all of these databases into one database. Our thoughts are to add a siteId column, 001, 002, 003, ..., 999. We are exploring options to make thi...

MSSQL server and index join

Hi, I'm working on an assignment where I'm supposed to compare different join methods in SQL Server, namely hash-join, merge-join and index-join. I'm having difficulties getting SQL Server to perform an index-join. Can anyone show me how I can force it to use an index-join (using a join hint or similar), or just simply provide a simpl...

Deleting a record ( using GridView )

hi 1) Why can we update data records ( via data source control ) without setting GridView.DataKeyNames property, but when deleting ( and if ConflictDetection property is set to the OverwriteChanges ), DataKeyNames must be set, else GridView will not pass parameters to data source control? Thank you ...

C# Reading SQL tables and views

Hi all, I'm currently reading in a list of tables in a SQL database and populating a comboBox with the table names. I want to include SQL Views in the same list. The sysobjects type for tables is 'U' and for views it's 'V'. How would I alter the OdbcCommand line to retrieve both U and V? Thanks. OdbcConnection cn=getConnection(); Odbc...

Where should I sell my website?

It is not 'complete' and earns no money at the moment, however I think it is a good idea and the design is very good. It is backed by a SQL database and is written in PHP. ALL the design and coding are original (done by myself). I just don't have time to keep working on it, but it seems like a waste to let it die. Given that the person ...

Problem with adding custom sql to finder condition

I am trying to add the following custom sql to a finder condition and there is something not quite right.. I am not an sql expert but had this worked out with a friend who is..(yet they are not familiar with rubyonrails or activerecord or finder) status_search = "select p.* from policies p where exists ...

Ruby on Rails calculating "rank" based upon database values?

Is there an easy and fast way to calculate the rank of a field in a database using Ruby on Rails? For example, if I have a math_scores table, and would like to find a given a MathScore.find(:all, :condtions => ... :order =>...) then iterate through all of them to find out where the test score falls, but there's got to be a more straight...

Split multiple SQL statements into individual SQL statements

Intro note: I'm hoping a library or routine exists to do this, but I haven't been able to find anything like this. I'm really looking for direction and advice on where to start... Here is the situation: I have a block of SQL commands coming as plain text. It might be one SQL commands or several. I need a way to split multiple SQL comman...

In a database, would you use a date field or year and month fields if you only need year and month?

I am setting up a table where I need the year and month. In MySQL I believe I have 2 options: (1) 2 fields: 1 for year, 1 for month or (2) a date field (the day would always be 1). The 2 fields has the advantage of being some what faster (I think) because MySQL doesn't have to convert the value from a date to an integer although this is...

How may I store contact methods (or similar typed data with different fields) in a normalized way?

I've crossed this problem in, oh, every application I've ever written. I'd like a final consensus answer! Is this the most normalized/efficient/proper way to store contact methods or any other data along this pattern in a database? Contact { PK ContactId, ... } ContactContactMethod {PK FK ContactId, PK FK ContactMethodId, Key, Commen...

Wildcard in query for ASP.NET GridView

I am using GridView in ASP.NET 2.0. I am want to show the details from 3 tables (SQL2005) in the GridView per my search crieteria (Name of Visitor,Passport Number,Name of Company). It is working, but I want to use a wildcard for searching by first letter of "Name of Visitor". I have my code in the QueryBuilder in GridView (using Configur...

How can I access a postgres table by name?

Accessing Tables by Name I need to reference a table name by a string value given to me. How would I go about doing this in Postgres? Using stored procedures with PLPGSQL is an option, however other languages will be unavailable. For example: SELECT count(*) FROM some_function_that_returns_data_by_table_name('mytable'); I have the f...