SQL Query analyser - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

SQL Query analyser

Author

Topic

jamesgould
Starting Member

3 Posts

Posted - 2008-11-20 : 04:39:18

Morning guys

Please bear with me as I am completely new to SQL and need some advice. Firstly, this is what I am trying to do. We have an old case management system with an SQL database sitting behind it. The case management application references two databases both the Case management and the account database.

Soon we are going to be transfering across to a new case management software that has Progress as its database. We will be transfering files in blocks of about 300 so I am trying to use SQL query Analyser to export to a csv file to then import into the new system.

So far I have written a select commmand as follows:

SELECT distinct MatFeeEarner, MatStatus, MatRef
From Database1.DBO.Matter
where Database1.DBO.Matter.MatStatus = 'O' and MAtFeeEarner = '606'

This works fine and returns a result of 327 records which ties up exactly with what is showing on the current case management system.

Now comes the problem, I need to reference the other database (database2) to add in other columns into the result so I did the following:

SELECT distinct MatFeeEarner, MatStatus, MatRef, Database2.DBO.PIAccidentdetails.AccidentDate
From Database1.DBO.Matter, Database2.DBO.PIAccidentDetails
where Database1.DBO.Matter.MatStatus = 'O' and MAtFeeEarner = '606'

This returns a result in the thousands. I was wondering if anyone could point out what I am missing. Basically I want to just display the data from Database2 and still keep the selection criteria stated in my first query.

Any help on this matter would be greatly appreciated

karthickbabu
Posting Yak Master

151 Posts

Posted - 2008-11-20 : 04:48:40

What is the link between these two tables in Database1, Database2?

You try some common fields to compare the tables

For ex:

Mat_ID in database1 and PI_ID in database2. They have the same values in two tables on Database1 & Database2. Then you add one more condition in your select query.

SELECT distinct MatFeeEarner, MatStatus, MatRef, Database2.DBO.PIAccidentdetails.AccidentDate
From Database1.DBO.Matter, Database2.DBO.PIAccidentDetails
where Database1.DBO.Matter.MatStatus = 'O' and MAtFeeEarner = '606'
AND Database1.DBO.Matter.Mat_ID = Database2.DBO.PIAccidentDetails.PI_ID

Likewise, Is there any fields to refer the both tables.

====================================================
you realize you've made a mistake, take immediate steps to correct it.

jamesgould
Starting Member

3 Posts

Posted - 2008-11-20 : 05:52:15

Thanks very much for the quick response I will have a look for two tables with common fields.
Will update on how I get on.

jamesgould
Starting Member

3 Posts

Posted - 2008-11-24 : 08:57:47

Thanks very much got around to sorting this out this morning and it all works.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-24 : 09:08:46

Also learn the ANSI JOIN syntax.

SELECT		s.MatFeeEarner,
		s.MatStatus,
		s.MatRef,
		Database2.DBO.PIAccidentdetails.AccidentDate
FROM		Database1.DBO.Matter AS s
INNER JOIN	Database2.DBO.PIAccidentDetails AS t ON t.PI_ID = s.Mat_ID
WHERE		s.MatStatus = 'O'
		AND s.MAtFeeEarner = '606'

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-24 : 09:16:00

quote:

Originally posted by Peso

Also learn the ANSI JOIN syntax.

SELECT		s.MatFeeEarner,
		s.MatStatus,
		s.MatRef,
		t.AccidentDate
FROM		Database1.DBO.Matter AS s
INNER JOIN	Database2.DBO.PIAccidentDetails AS t ON t.PI_ID = s.Mat_ID
WHERE		s.MatStatus = 'O'
		AND s.MAtFeeEarner = '606'

E 12°55'05.63"
N 56°04'39.26"

No need of repeating the full name. you can use alias instead

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources