sql

Many-to-many relationship: use associative table or delimited values in a column?

Update 2009.04.24 The main point of my question is not developer confusion and what to do about it. The point is to understand when delimited values are the right solution. I've seen delimited data used in commercial product databases (Ektron lol). SQL Server even has an XML datatype, so that could be used for the same purpose as del...

SQL Server 2005: How to add a column to a table at the beginning of the table?

Good day, When we add a column to a table, it gets added to the end of the table. However, I really need to add a column to the beginning of the table. The reason is that we have scripts that import data from a flat file source to a table, and that it would be really easier for us to have the columns at the beginning to the table. Tha...

SQL - Nested select statement ?

I have 2 tables, ticket and ticket_custom. Here are how the tables are set up. I have a web interface where I can change the state (value in the table ticket_custom) The web interface adds a new entry instead of updating the original entry. ticket name value 1 state Ready for Final Verification 2 state Ready for Final Verificatio...

validation on email / postcode fields in sql/oracle

Hi, Would be gratefull for some advice on the following - Is it possible to validate email and postcode fields through some kind of check constraint in the sql in oracle ? or this kind of thing as i suspect pl/sql with regular expressions ? Thanks ...

Access DB update one table with value from another

I'm trying to update all records in one table with the values found in another table. I've tried many versions of the same basic query and always get the same error message: Operation must use an updateable query. Any thoughts on why this query won't work in Access DB? UPDATE inventoryDetails as idet SET idet.itemDesc = ( ...

[Mysql] Fetch all news and all comments

Hi, I'm trying to code a query that gets all the news and all the comments for each news. My current query is : SELECT n.*, c.* AS comments FROM news n JOIN comments c ON (c.news_id = n.id) But when I fetch the query as an array it gives me a key by comment and I would like to have a key by news and all comments in a s...

How do I loop through a PHP array containing data returned from MySQL?

Ok I have a table with a few fields. One of the fields is username. There are many times where the username is the same, for example: username: bob password: bob report: 1 username: bob password: bob report: 2 I did a SQL statement to select * where username='bob'; but when I do the following PHP function, it will only retur...

SQL Iterators

Does anyone know good source where I can find about implementation of SQL iterator/Operator in java and any other languages? Than you, -Nimesh ...

SQL Server and message queues

I'm trying to build a reliable message service, or at least that's how I would describe it. Here's my problem: I have a table, I insert data into this table, I have at least two applications which select data from this table. However, I need a reliable way for the two different applications to never select the same rows at any given tim...

Getting distinct rows from a left outer join

I am building an application which dynamically generates sql to search for rows of a particular Table (this is the main domain class, like an Employee). There are three tables Table1, Table2 and Table1Table2Map. Table1 has a many to many relationship with Table2, and is mapped through Table1Table2Map table. But since Table1 is my main t...

Is the SQL WHERE clause short-circuit evaluated?

For example: SELECT * FROM Table t WHERE @key IS NULL OR (@key IS NOT NULL AND @key = t.Key) If @key IS NULL evaluates to true, is @key IS NOT NULL AND @key = t.Key evaluated? If No, why not? If Yes, is it guaranteed? Is it part of ANSI SQL or is it database specific? If database specific, SqlServer? Oracle? MySQL? Reference: S...

What the best way to handle categories, sub-categories - hierachical data?

Duplicate: SQL - how to store and navigate hierarchies If I have a database where the client requires categories, sub-categories, sub-sub-categories and so on, what's the best way to do that? If they only needed three, and always knew they'd need three I could just create three tables cat, subcat, subsubcat, or the like. But wh...

Changing SQL CONSTRAINT Based On Other Fields

I have an SQL table written in MSSQL: create table [action] ( action_id bigint identity not null, -- PK action_action char(1) not null, -- 'C' Call, 'R' Raise, 'F' Fold, 'P' Post action_size decimal(9,2) not null, -- zero if fold, > zero otherwise constraint pk_action primary key clustered (action_id), constraint...

Can this be written in a better way?

I have these two tables: CREATE TABLE x ( id INT NOT NULL, exclude BIT NOT NULL, PRIMARY KEY (id) ); CREATE TABLE y ( id INT NOT NULL, field1 INT NOT NULL, field2 INT NOT NULL, field3 INT NOT NULL, field4 INT NOT NULL, field5 INT NOT NULL, PRIMARY KEY (id) ); And this sproc: CREATE PROCEDURE p...

How do I match two identical database tables with LINQ?

I want to match 2 identical tables: sourceProducts (productName, ProductionDate, ManID, shipper, distributer) CommProducts (productName, ProductionDate, ManID, shipper, distributer) but the number of rows and the record contents may differ. How do I select a certain record = raw from one table and get its clone record from the other...

SQL: Use the same string for both INSERT and UPDATE?

The INSERT syntax I've been using is this INSERT INTO TableName VALUES (...) The UPDATE syntax I've been using is UPDATE TableName SET ColumnName=Value WHERE ... So in all my code, I have to generate 2 strings, which would result in something like this insertStr = "(27, 'John Brown', 102)"; updateStr = "ID=27, Name='John Brown', I...

SQL: Do you need an auto-incremental primary key for Many-Many tables?

Say you have a Many-Many table between Artists and Fans. When it comes to designing the table, do you design the table like such: ArtistFans ArtistFanID (PK) ArtistID (FK) UserID (FK) (ArtistID and UserID will then be contrained with a Unique Constraint to prevent duplicate data) Or do you build use a compound PK for...

sql server 2005: Value truncation

My table contains a field password whose size is Varchar(70). But when I enter the hash value of the password which is of size 40 the last few characters are getting truncated. I'm using SQL SERVER 2005. Why is it so?? ...

mysql warnings

Hello everyone. I have the following table: CREATE TABLE `events` ( `evt_id` int(11) NOT NULL AUTO_INCREMENT, `evt_name` varchar(50) NOT NULL, `evt_description` varchar(100) DEFAULT NULL, `evt_startdate` date NOT NULL, `evt_enddate` date DEFAULT NULL, `evt_starttime` time DEFAULT NULL, `evt_endtime` time DEFAULT NULL, ...

How do I return data from multiple MySQL tables in date order?

I have another MySQL problem. Again, with the complex queries I'm in over my head. On my website I currently display the last 5 reviewed games on the main page. Now, it's been ticking over a while and I've decided to add news items as well to the same page. But, I want to change the system that I have into one that displays news entrie...