-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 =
...
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!
...
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...
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...
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
...
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...
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!
...
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...
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')
...
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:...
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...
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...
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...
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.
...
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...
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):
...
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...
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...
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,
...
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...