sql

How do i write a SQL statement with mutliple test and tables?

-edit- Thanks everyone i figured it out with your suggestions and syntax you wrote. This is what i am attempting to do but for the life of me i cant figure out how to write the SQL statement (sqlite until prototype is done, will switch to another SQL which i havent decided upon yet). Solution: command.CommandText = ...

SQL division

I have three tables; Apartment (apartmentnr, floor, apartmenttype), Floor (floornr, house), House(housenr, adress) Now, I want to show housenr and adresses of houses that have apartments of all apartmenttypes (1-4), using 'NOT EXISTS'. Please help me with suggestions on how to do this! ...

What are some of your most useful database standards?

I have some ideas, but I really want to know what makes things go smoothly for you when modeling database: Table name matches Primary Key name and description key Schemas are by functional area Avoid composite primary keys where possible (use unique constraints) Camel Case table names and field names Do not prefix tables with tbl_, or...

How is this SQL leading to ON UPDATE CURRENT_TIMESTAMP?

Here's a snippet of my PHP that is creating the table: $sql = 'CREATE TABLE '.$table.' ( `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY , `name` VARCHAR( 55 ) NOT NULL , `venue` VARCHAR( 55 ) NOT NULL , `time` TIMESTAMP NOT NULL , `desc` TEXT NOT NULL )'; This is making the time column become th...

Visual Studio Macros

I am creating a Visual Studio Macro and I want it to take the data it is generating and export it to SQL. I can't seem to find a good way to connect to a database from Visual Studio's VBA. Does anyone know of a good way to do this? I am running Visual Studio 2008 and SQL Server 2008. Thanks for any help ...

SQL Select Question

I using this in Access 2003. Pardon me if this is in the wrong spot, but I hoped someone could help. I have a table with multiple records that have a text represented date field. The date is in a format like: "06/01/2009" I need to select all the fields from the table but only the 6 oldest rows that fall in a range for each group of: C...

MS SQL Server fastest way of changing column definition

Hi, I am tasked with changing a table's column datatype from smallint to int for a table that has 32 million rows in it. The requirement is to have the lowest downtime possible (basically the fastest way of achieving this). We are using SQL Server 2000. Please help! ...

Refactored SQL projection?

I don't like having the same thing defined in two places, if I can avoid it. I realize the two queries below are dealing with two different tables, but those tables hold basically the same kind of data (distinct predicates warrant the two queries), and I think of the two projections below as "the same thing defined in two places". When...

How do I return a new IDENTITY column value from an SQLServer SELECT statement?

I'm inserting into an SQLServer table with an autoincrementing key field. (I believe this is called an IDENTITY column in SQLServer.) In Oracle, I can use the RETURNING keyword to give my INSERT statement a results set like a SELECT query that will return the generated value: INSERT INTO table (foreign_key1, value) VALUES (9, 'text') ...

How can I convert a SQL Server date format to Oracle?

I am working on migration of data from an old system to a new system. As part of migration, the data from the legacy system, (stored in files) is pumped into MS SQL Server. Now my app runs on Oracle. I'm having a problem with the date/timestamp. The timestamp format in MS SQL Server data is: 2008.12.23 00:00:00 Oracle expects:...

Dealing with a varbinary field in VB.NET

A partner of ours is making a call to a web service that includes a parameter called token. Token is the result of an MD5 hash of two numbers, and helps us authenticate that the user using our partners system. Our partner asks the user for two strings, concatenates them, runs them through MD5, and then calls our web service. The result...

Linq group by with a sub query

Hello, I have an object that contains these three properties: ID, DATE and NAME and want to query it with linq yielding the same results as if written in the sql statement below. SELECT ID, MAX(DATE), NAME FROM TABLE GROUP BY ID Here is what I have so far with linq: var grouped = from a in table group a by a.ID into g select new fo...

Iterating through a large table with performance in mind

If I need to check an entire table to see if a condition is true (e.g. every Ticket column has an ID with a certain suffix). What would be a good way of going about this, performance wise? The table is quite large so if I go through every row, that's a lot of time hitting the database. Cursors are slow, so that wouldn't really be an ele...

Parsing SQL text

Does anyone know how to parse SQL Text with VB.NET? Ex: I got a sql file "CREATE TABLE..." i want to get an array of columns and an array of data types. ...

How to make Oracle Query recognizable to ASP.NET table adapter?

I have a query with a sub query in the select that needs to get a value from a certain table if it exists. I don't want to paste the query here for business reasons, but here's a simplified example of what I am trying to do: select a, b, (select x from z) as c from table where ... The query runs fine in TOAD, but when I go through t...

Injecting a value into text

I have some contract text that may change. It is currently (non live system) stored in a database field (we determine which contract text to get by using other fields in table). I, however need to display a specific date (based on individual contract) within this text. IE (but Jan 12, changes depending on the individual contract): ...

Why does adding count(*) to a select statement force a row to exist in a subquery?

On Oracle 9i, why does the following produce the result 'abc' select 'abc ' || (select txt from (select 'xyz' as txt from dual where 1=2)) from dual while this produces 'abc xyz': select 'abc ' || (select txt from (select count(*), 'xyz' as txt from dual where 1=2)) from dual Why does adding count(*) to the subquery r...

Many to Many Relation Design - Intersection Table Design

I'm wondering what a better design is for the intersection table for a many-to-many relationship. The two approaches I am considering are: CREATE TABLE SomeIntersection ( IntersectionId UNIQUEIDENTIFIER PRIMARY KEY, TableAId UNIQUEIDENTIFIER REFERENCES TableA NOT NULL, TableBId UNIQUEIDENTIFIER REFERENCES TableB NOT NUL...

Filter SQL query by a unique set of column values, regardless of their order

I have a table in Oracle containing two columns that I'd like to query for records containing a unique combination of values, regardless of the order of those values. For example, if I have the following table: create table RELATIONSHIPS ( PERSON_1 number not null, PERSON_2 number not null, RELATIONSHIP number not null, ...

How to get entries of current date portion from column with datetime as datatype (TSQL)

Hi, I have daily scheduled task that queries a table which includes column named AttemptDate with datetime as datatype. However the task queries all entries regardless of the date. So when the tasks executes, it outputs entries of all the dates as shown below: 2009-06-06 06:01:30.852 2009-06-07 01:41:46.719 2009-06-08 03:58:23.945...