Joining Tables - Database Discussion Boards

Farhan Noor Qureshi26-Oct-05 11:35

26-Oct-05 11:35

Use nested query. Something like,

Select sum_of_data, your_function(sum_of_data)
From (Select sum(...) as sum_of_data from orders Where ....) as Temp_Results

Farhan Noor Qureshi

^{if (this == this) thow this;}

HiiSQL Server Database Name

utsav_verma24-Oct-05 19:48

utsav_verma

24-Oct-05 19:48

Hii

i need to get a list of all the database in the sql server 2000.

Thanks

Utsav

Re: HiiSQL Server Database Name

Colin Angus Mackay24-Oct-05 20:13

Colin Angus Mackay

24-Oct-05 20:13

Connect to the master database and run the following:

SELECT name FROM sysdatabases

Does this help?

My: Blog | Photos

"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious

raw_Open

Karim Shehadeh24-Oct-05 11:03

Karim Shehadeh

24-Oct-05 11:03

I'm developing a multi-threaded application that communicates with SQL Server via ADO. The application is written in MFC and is being tested on WindowsXP SP2. The problem I'm having is that the following function call will hang the application intermittently and I'm not sure why: Recordset15::Open (part of the ADO API). More specifically, it seems to happen deep within the raw_Open call. Although there are multiple threads running, I've made it so that only the one thread is actually communicating with the ADO database. And, besides, I was under the assumption that the ADO was a thread-safe API. Also, I'm not sharing connections across different threads.

Any ideas?

Thanks,

Karim Shehadeh
http://www.iwonderdesigns.com

Last unique inserted row

Anonymous24-Oct-05 1:14

Anonymous

24-Oct-05 1:14

I need to retrieve the last inserted row for unique set of columns

Got a table with

AutoIncrement , Position , status , dateTime

where position is a nr form 1 to 20 and status a nr from 1 to 50

The table get updated continuously and I need to now the last status for every position

Re: Last unique inserted row

miah alom24-Oct-05 4:50

miah alom

24-Oct-05 4:50

select * from ctm group by AutoIncrement having Position = max(Position) and Status = Max(Status)

should do it.

Re: Last unique inserted row

toxcct25-Oct-05 0:24

toxcct

25-Oct-05 0:24

select * from mytable where myColumn = @@IDENTITY

see here[^] for more details

TOXCCT >>> GEII power
[toxcct][VisualCalc]

Joining Tables

Greeky23-Oct-05 21:53

Greeky

23-Oct-05 21:53

Imagine two table
One Table (ForeignCurrency)

FC_ID FC_TYPE
1 USD
2 EUR
3 YTL

Other table (Rates)

FCV_ID FCV_RateIN FCV_RateOUT FCV_DATE
1 1 1 10/13/2005
2 1.345 1.346 10/13/2005
3 1.651 1.654 10/13/2005

I need to join two table in a one query. I do this so
Select FC.FC_TYPE,ISNULL(FCV_RateIN,1) FCV_RateIN,ISNULL(FCV_RateOut,1) FCV_RateOut from ForeignCurrency FC LEFT OUTER JOIN Rates R ON FC.FC_ID=R.FCV_ID WHERE FCV_Date='10/13/2005'

This give me result as i want. But when i need 10/14/2005 rates it give me empty result set , but i expect
FCV_TYPE FCV_RateIn FCV_RateOUT
YTL 1 1
USD 1 1
EUR 1 1

How can i re-write code ?

Re: Joining Tables

miah alom24-Oct-05 4:54

miah alom

24-Oct-05 4:54

If you want the resultset independent of the FCV_DATE column just remove it from the where clause.
Since there is no column with FCV_Date='10/14/2005' you get an empty resultset.

Using where clause with .CSV files

Adeel68823-Oct-05 19:15

Adeel688

23-Oct-05 19:15

I use ODBC programming in order to connect VC++ with database. For that i First create system DSN, Use CDatabase class SQLConnect(...) function in order to connect to the DSN, Then use SQLExecDirect in order to run the querry. But i am facing problem with CSV files.

I Created a system DSN of Microsoft Text Driver and then connected it worked. As you know in CSV files it automatically takes first rows as the field name. Therefore if I run the query "SELECT * FROM TableName.csv", IT works. But if I write a querry "SELECT Col1, Col2,....Coln FROM TableName.csv" It does'nt work cuz it is unable to recognize and individual column. Now I need to run a querry in order to check for an existing data "SELECT * FROM TableName.csv WHERE Col1 = 'MyName'" even this query generates an error.

So can anyone help me how to reslove this selection problem? Confused | :confused:

ADO SECURITY WARNING!

delnaz23-Oct-05 6:52

delnaz

23-Oct-05 6:52

safety setting on this computer prohibit accessing a data source on another domain.

Have good day and time

ADO WARNING SECURITY

delnaz23-Oct-05 0:10

delnaz

23-Oct-05 0:10

Hello there i have a connectionstring like below:
var connectionstring = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=false;Data Source=E:\\mohebi\\b1384.6.26\\adabiat1\\data base\\db_demo.mdb";

and when i wana open it on IIS on my local computer, i have this ADO Security Warning :

This website is using your identity to access a data source.If you trust this website ,
click OK to continue ,otherwise click Cancel.

I found the way to deny this warning and that is adding my website to TRUSTED SITES in IE.

BUT when i use this source on MY SERVER ,MY SERVER is WINDOWS SERVER 2003, it is another warning like below:

safety setting on this computer prohibit accessing a data source on another domain.

I can solve this problem with adding it to TRUSTED SITES , but i have many clients , AND OF COURSE I CANT DO THIS FOR ALL MY CLIENTS!!!

how can i solve this problem ??

Have good day and time

Re: ADO WARNING SECURITY

S Douglas7-Nov-05 23:03

S Douglas

7-Nov-05 23:03

delnaz wrote:
safety setting on this computer prohibit accessing a data source on another domain.

As memory serves that’s because you’re running the code client side. You will need to mod it to run server side.

DEBUGGING : Removing the needles from the haystack.

mysql query browser (newbie) - running multiple queries

tjawed22-Oct-05 8:50

tjawed

22-Oct-05 8:50

Okay so I am a complete noob to mysql.

I am trying to run the following statements in mysql query browser

DROP TABLE IF EXISTS `bedbugs`.`airports`;

CREATE TABLE `bedbugs`.`airports` (
`city` char(20) default NULL,
`country` char(20) default NULL,
`name` char(20) NOT NULL default '',
PRIMARY KEY (`name`)
);

basically two different queries through one query window.

I get the following error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ';
CREATE TABLE `bedbugs`.`airports` (
`city` char(20) default NULL,
`cou' at line 1

Can someone explain what is going on. If I comment out either query - the other one runs fine (basically I can run one at a time).

Is this some sort of limitation of the query browser?
thanks in advance

Re: mysql query browser (newbie) - running multiple queries

dabuskol23-Oct-05 19:52

dabuskol

23-Oct-05 19:52

-- =============================================
-- Create table basic template
-- =============================================

IF EXISTS(SELECT name <br />
	  FROM 	 sysobjects <br />
	  WHERE  name = N'<table_name, sysname, test_table>' <br />
	  AND 	 type = 'U')<br />
    DROP TABLE <table_name, sysname, test_table><br />
GO<br />
<br />
CREATE TABLE <table_name, sysname, test_table> (<br />
<column_1, sysname, c1> <datatype_for_column_1, , int> NULL, <br />
<column_2, sysname, c2> <datatype_for_column_2, , int> NOT NULL)<br />
GO

I beleive you cannot drop or create two table in 1 statement.
Try the template above.

Tutotial Link[modified]

ddmcr22-Oct-05 3:39

ddmcr

22-Oct-05 3:39

Hello guys,

Can anybody give me a link of a website , where is well explained how to
edit , delete and add records in a *.mdb dadatase file.

Or any good tutorial on how to use CDaoDatabase class

I have a MFC Dialog Based Application
And I am using Visual Studio.Net 2003

Thanks in advance

"Success is the ability to go from one failure to another with no loss of enthusiasm." - W.Churchill

-- modified at 12:24 Monday 24th October, 2005

Re: Tutotial Link

Stanciu Vlad22-Oct-05 7:51

Stanciu Vlad

22-Oct-05 7:51

mdb database files are also known as Access databases. You could try this link http://www.codeproject.com/cs/database/csharpaccessdb.asp[^] or any other article present in the C# Database Section[^].
Basicly you can handle that type of database trough OleDb or Odbc in the same ADO.NET manner but using diferent implementations...

I hope you understand...because is a rough world out there...

Re: Tutotial Link

ddmcr22-Oct-05 10:31

ddmcr

22-Oct-05 10:31

thanks , but I need to implement it in C++

"Success is the ability to go from one failure to another with no loss of enthusiasm." - W.Churchill

Deleting duplicate rows based on timestamps ?

Aditi48422-Oct-05 2:23

Aditi484

22-Oct-05 2:23

hello..

I'm using MYSQL and have a table with two fields :tag_in and time_in, where time_in is a timestamp field..

Some rows have the same value for tag_in.. I'd like to neaten up the database in 2 ways:

1. Delete rows with duplicate tag_in but removing the one with the oldest timestamp value

2. Delete rows with duplicate tag_in but removing the one with the latest timestamp value

how can i go about this please ?

Re: Deleting duplicate rows based on timestamps ?

dabuskol23-Oct-05 19:39

dabuskol

23-Oct-05 19:39

Get the minimum - select min(tag_in)
Get the maximum - select max(tag_in)

Re: Deleting duplicate rows based on timestamps ?

Aditi48423-Oct-05 21:29

Aditi484

23-Oct-05 21:29

thanks for the reply.. but that just gives me one value of tag_in which is the smallest out of the whole table.. or am i overlooking something ??

SQL datediff

dabuskol22-Oct-05 0:14

dabuskol

22-Oct-05 0:14

if (

select count(*) as EmtCnt from dtemdt where emdtactive = 0<br />
          and datediff('d',dtetime,getdate()) > 1

)
begin
--delete from dtemdt where emdtactive = 0 and datediff('hh',dtetime,getdate()) > 24
end

Please tell me the error, I can't figure it out.

Re: SQL datediff

Colin Angus Mackay23-Oct-05 5:55

Colin Angus Mackay

23-Oct-05 5:55

What is the error message - or what is the result set you are getting compared with the result set you expected to get?

Also, you may like to look into IF EXISTS (SELECT * FROM ....) it is faster than using COUNT(*) especially if there is a large amount of data.

My: Blog | Photos

"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious

Re: SQL datediff

dabuskol23-Oct-05 19:36

dabuskol

23-Oct-05 19:36

Error Message after running the SQL from SQL analyzer

Server: Msg 1023, Level 15, State 1, Line 2
Invalid parameter 1 specified for datediff.

Re: SQL datediff

Colin Angus Mackay23-Oct-05 20:34

Colin Angus Mackay

23-Oct-05 20:34

Don't put the d inside apostrophes

My: Blog | Photos

"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.