Hi, I am trying to Join Multiple Tables. Table 1 has distinct ID's Table 2 has multiple names for each ID SAY 3 NAMES Table 3 has 2 dates for each ID when i join the Three tables, I get 6 rows of data for each ID with each of the Names appearing Twice and each of the dates appearing thrice. I want only 3 rows to be written returning d...
I'm writing a query to do some stuff. But its not working the way I want it to: select CORR_ID from TABLE1 where CORR_ID not in (select id from TABLE2) The problem is, TABLE2.id is a long, while TABLE1.CORR_ID is a string. So how can I make it work? PS: I'm using IBM UDB. ...
declare @fieldForceCounter as int declare @SaleDate as dateTime declare @RandomNoSeed as decimal set @fieldForceCounter = 1 set @SaleDate = '1 Jan 2009' set @RandomNoSeed = 0.0 WHILE @fieldForceCounter <= 3 BEGIN while @SaleDate <= '1 Dec 2009' begin INSERT INTO MonthlySales(FFCode, SaleDate, SaleValue) VALUES(@fie...
Given a table: create table #orders ( orderid int, orderdatetime datetime ) What is the best way to write sql to output a report containing the count of orders from the current and previous 24 hours, total orders for the current day and previous 7 days, total orders for the current week and previous 4 weeks, and total orders f...
I keep getting the following error when I try to register a db server: TITLE: Connect to Server Cannot connect to p3swhsql-v14.shr.phx3.secureserver.net. ADDITIONAL INFORMATION: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that u...
What should be the most recommended datatype for storing an IPv4 address in SQL server? Or maybe someone has already created a user SQL data-type (.Net assembly) for it? I don't need sorting. ...
After setting up a table model in Qt 4.4 like this: QSqlTableModel *sqlmodel = new QSqlTableModel(); sqlmodel->setTable("Names"); sqlmodel->setEditStrategy(QSqlTableModel::OnFieldChange); sqlmodel->select(); sqlmodel->removeColumn(0); tableView->setModel(sqlmodel); tableView->show(); the content is displa...
The project I'm working on has an Oracle backend, and we use SQL Navigator 5.5 for SQL development and it really sucks: the intellisense doesn't work for queries with more than one table and you can't browse the schema and write a query on the screen at the same time. Not fun. I also tried using Oracle SQL Developer. That's not much o...
Guys, This is a two-pronged question: Scenario: I have a script to query MSDB and get me details of job schedules. Obviously, the tables differ from SQL 2000 to SQL 2005. Hence, I want to check the version running on the box and query accordingly. Now the questions: Question 1: This is what I am doing. IF LEFT(CAST(SERVERPROPERTY('P...
I want to create a website that the login system shouldn't be handled by cookies, but on (a) table(s) in the local (on the server) SQL DB. Is there a way to do it? Even no partial way? What and where should I save instead of the cookie??? ...
Can you experts give me some thougths on this code? Some security hole i have missed? Can you see any potential threats? Something i can do better? I'm still learning :) Thanks <?php if (isset($_POST['username'])) { $username = mysql_real_escape_string($_POST['username']); $password = mysql_real_escape_string($_POST['password']); $pa...
I have a SQL job that simply executes a stored procedure. Each morning when the job attempts to run, I get the following error: The current transaction cannot be committed and cannot support operations that write to the log file. When I keep trying to restart the job, it keeps giving me that same error. However, if I just execute the...
Hi I'm looking to optimize my SQL. My database schema is: HOMES home_id address city state zip primary_photo_group_id HOME_PHOTOS photo_id (primary key) home_id (home primary key) photo_group_id (a photo group is the same image, resize from thumbnail to large size) home_photo_type_id (the size of the image be it a thumbnail or a...
Hi, I have two tables: CREATE TABLE dbo.country ( cntry_id VARCHAR(2) NOT NULL, name VARCHAR(50) NOT NULL, CONSTRAINT pk_country PRIMARY KEY (cntry_id) CREATE TABLE dbo.city ( city_id VARCHAR(3) NOT NULL, name VARCHAR(50) NOT NULL, cntry_id VARCHAR(2) NOT NULL, CONSTRAINT pk_city PRIMARY KEY (city_id), FO...
Using SQL, I have 5 columns: ssn, lastname, firstname, RF and a flag field. I need to go through this table and where the 4 columns are equal to another row and the value of the flag field in that row is equal to 2050, then delete that 2050 record. ...
It appears my SQL isn't limiting results based on price. In my previous post, http://stackoverflow.com/questions/1039771/sql-help-me-optimize-my-sql, people indicated that I should use a LEFT OUTER JOIN. SELECT homes.home_id, address, city, state, zip, price, photo_id, photo_url_dir FROM homes LEFT OU...
Hi there, I would like to know if it is possible to do a wildcard search using LINQ. I see LINQ has Contains, StartsWith, EndsWith, etc. What if I want something like %Test if%it work%, how do I do it? Regards ...
We have an Interbase 7.1 database and I'm trying to figure out how to limit the number of records returned by the query to just 1. I really only need to know an event code from the last record, in which the query will return hundreds of records if I cannot do some kind of limit. Thanks in advance! ...
I have a string '2009-06-24 09:52:43.000', which I need to insert to a DateTime column of a table. But I don't care about the time, just want to insert it as 2009-06-24 00:00:00.000 How can I do that in T-SQL? ...
enter code hereI have a table on SQL server 2005 with bigint primary key and MS Access 2003 front end (linked tables). I want to update the record for the table that has bigint as primarykey. I use CurentDb.execute _ "Update myTable SET Field1 =1 , Field2 = 'test' WHERE bigintKey = " & myVar I get Data mismatch in criteria expre...