sql

Using LEFT on a TEXT-field in SQL Server

In a table, I have a text-field. I need to be able to select only the first 200 chars of the field - but LEFT does not work with TEXT-fields. What to do? ...

SQL Server TSQL conventions/patterns on SET, WITH and ENABLE

After years of using TSQL, I still cannot figure out when to use SET, WITH or ENABLE. When you read TSQL statement like, ALTER TABLE Person.Person ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON); It looks more intuitive and readable if it was written like (invalid query below), ALTER TABLE Person.Person SET CHANGE_TRACKI...

SQL UPDATE - IS there a more elegant and efficient way of doing this?

I have 3 tables Site, Price and PriceMonth. Site has many Prices (joined on siteId). PriceMonth holds a date which is a looked up by Price (joined on PriceMonth). The idea is that the price records are created when a site is created as place holders. When a user clicks a Price row in a view - I need to update the prices for the res...

ID should not display between the specified date?

Using SQL Server 2000 Database1.Table1 ID Name Date title 001 Ravi 23-02-2009 Accountant 001 Ravi 24-02-2009 Accountant 001 Ravi 25-02-2009 Accountant 002 Ram 21-02-2009 Supervisors 002 Ram 25-02-2009 Supervisors 002 Ram 26-02-2009 Supervisors So on…, Database2.Table2 ID Name Date1 Date2 001 Ravi 23-02-2009 ...

selecting results by postcode / longitude / latitude

i have 3 tables: 1 contains people with their city (comes from other table) and the range (int, 1-20) they want to be found in (in miles) 1 contains cities and the beginning of the postcode (i.e. BB2) 1 contains cities and their logitudes/latitudes what i am trying to do is select the people according to a postcode someone enters. if th...

SQL Server 2008 Unique Column that is Case Sensitive.

Is there a way to make a column both UNIQUE and Case Sensitive? I want to be able to put abcde and ABCDE in a unique column. ...

MYSQL get amount of results...

When you use mysql, you can do something like this: SELECT * FROM student WHERE teacher_id = 1 ...then you get the amount of results, and with that the result. But I only want the amount of results but then for every teacher. Is that possible in mysql like sizeof or something? Also, if there are teachers that have no students is it...

MySQL GROUP_CONCAT headache

For performance,I need to set a limit for the GROUP_CONCAT, and I need to know if there are rows not included. How to do it? EDIT Let me provide a contrived example: create table t(qid integer unsigned,name varchar(30)); insert into t value(1,'test1'); insert into t value(1,'test2'); insert into t value(1,'test3'); select ...

Unable to create column in MySQL database with datatype of TEXT

I'm creating a table to hold items from rss feeds and I need to create a column 'description'. I selected the datatype as TEXT with no limit set with no index on this column. This is the error I'm getting: #1071 - Specified key was too long; max key length is 1000 bytes If I select to index this column, I get this error message: #117...

[SQL] Distinct multi-columns

Hi, For this table: mysql> select * from work; +------+---------+-------+ | code | surname | name | +------+---------+-------+ | 1 | John | Smith | | 2 | John | Smith | +------+---------+-------+ I'd like to get the pair of code where the names are equal, so I do this: select distinct A.code, B.code from work A, work B ...

Are there any online tutorials for tuple and domain relational calculus?

I'm looking for (free) online tutorials that teach the subjects as stated in the title, is there anyone who has any good ones to recommend? Thanks! ...

SQL DELETE falling over in PHPmyAdmin Cant work out Why, is it because of LEFT OUTER JOIN

SELECT * FROM `User` LEFT OUTER JOIN `freshersdata` ON `User`.`username`=`freshersdata`.`username` WHERE (`freshersdata`.`username` IS null) AND (`User`.`Persistent`!=1) This SQL query is falling over with ( #1064 - You have an error in your SQL syntax; ) message but works perfecly with a SELECT instead of a delete, why is th...

Prepared Statements with functions syntax

I'm getting an error when I'm trying to run this: mysql> prepare stmt1 from "UPDATE test.users SET password = password('?') WHERE usrid = ?"; mysql> prepare stmt1 from "UPDATE test.users SET password = password(?) WHERE usrid = ?"; How can I make a prepared statement where a function takes my variable as an argument? ...

a little sql problem

If the following SQL statements are executed in the order shown: CREATE TABLE orders (order_num INTEGER NOT NULL, Buyer_name VARCHAR(35), Amount NUMERIC(5,2)); CREATE UNIQUE INDEX idx_orderno ON orders(order_num); whenever the ORDERS table is queried rows should be displayed in order of increasing OR...

MYSQL select "all" of the values rather than "any" of them?

Hello this SQL statement "select firstname,lastname from users where lastname in('foo','bar') group by firstname" it means select all records in table "users" where lastname match any of the "foo" or "bar" I don't want this behaviour what I want to retrieve is all records in "users" table where lastnames match all of "foo" and "b...

MySQL auto_increment vs max

Alright, which is faster and better to use? Auto_increment or Call an sql which which does a max and then add one So ultimately the question is should you use mysql to do it or do it yourself? ...

MySQL Query Related Problem

setup: mysql> create table bank(bank_id integer, bank_name varchar(255)); Query OK, 0 rows affected (0.27 sec) mysql> create table accounts_bank(method tinyint, bank_id integer, amount float); Query OK, 0 rows affected (0.09 sec) mysql> insert into bank(bank_id, bank_name) values(1, 'A Bank'); Query OK, 1 row affected (0.05 sec) mys...

SQL query to collect entries from different tables - need an alternate to UNION

Hi guys, I'm running a sql query to get basic details from a number of tables. Sorted by the last update date field. Its terribly tricky and I'm thinking if there is an alternate to using the UNION clause instead...I'm working in PHP MYSQL. Actually I have a few tables containing news, articles, photos, events etc and need to collect al...

Exporting data In SQL Server as INSERT INTO...

I am using SQL Server 2008 Management Studio and have a table I want to migrate to a different db server. Is there any option to export the data as an insert into SQL script?? ...

Simple SQL (hopefully!) Query Question

Hi I hope someone can help me with my faltering steps to formulate a SQL query for the following problem. I have a simple table that records visitor names and dates. The relationship is many to many, in that for any given date there are many visitors, and for any given visitor there will be one or more dates (i.e. repeat visits). There ...