Query & join between 2 databases on same server

ahmz · #1 (**permalink**) 11-09-11, 21:16

Hi Guys,

I need a some help on a query I am tryin to do, any help would be greatly appreciated.

I am trying to query 2 SQL databases on the same server and inner joining together.

Databases: Goldmine_MIOA
Goldmine_GMBase

From the "Goldmine_MIOA" database I need to query contact1.* and from the "Goldmine_GMBase" database i need to query cal.*

I would then like to inner join both these together. Anyhelp will be appeciated

baburajv · #2 (**permalink**) 11-10-11, 00:02

Select GMC.*, GMB.*
From Goldmine_MIOA..Contact1 GMC
Inner Join Goldmine_GMBase..Cal GMB
On <use appropriate join condition here>

ahmz · #3 (**permalink**) 11-10-11, 06:19

Hi, sorry im not skilled at SQL i just know the basics so when it comes to inner joining and joining commands i suck lol. can you please provide me with query i can just copy and paste in sql query analyser:P

thanks

baburajv · #4 (**permalink**) 11-10-11, 06:34

Pls refer Using Inner Joins

write the query yourself. if it doesnt work then post the query

Wim · #5 (**permalink**) 11-10-11, 09:32

Quote:

Originally Posted by ahmz

Hi, sorry im not skilled at SQL i just know the basics so when it comes to inner joining and joining commands i suck lol. can you please provide me with query i can just copy and paste in sql query analyser:P

You did not gave enough information for anyone to do what you ask. We need to know the columns in both tables that define the relation between those tables, like "Contact1.callerId = Cal.id and Contact1.year = Cal.startYear" or whatever.

ahmz · #6 (**permalink**) 11-10-11, 15:39

Hi Sorry for the lack of information however this is the query i am trying to work on.

SELECT C1.* FROM CONTACT1 AS C1 (NOLOCK) JOIN (SELECT DISTINCT ACCOUNTNO FROM CAL WHERE (CAL.ONDATE >= '20111111' AND CAL.ONDATE <= '20111111') AND (CAL.RECTYPE in ('S'))) CL ON CL.AccountNo = C1.AccountNo JOIN (SELECT DISTINCT ACCOUNTNO FROM CONTHIST WHERE (CONTHIST.ONDATE >= '20111111' AND CONTHIST.ONDATE <= '20111111') AND (CONTHIST.REF LIKE '%Pharmacy Worksheet%')) CH ON CH.AccountNo = C1.AccountNo ORDER BY C1.CONTACT

Issues with this query:

1)The "CAL" table is located on the "GoldMine_GMBase" database

2) The "Conthist" Table s located on the "Goldmine_MIOA" database with the table name of "contact2"

3) the remainder of the query is in the "Goldmine_MIOA" database as "contact1"

All databases is stored on the same SQL server.

Any help will be greatly appreciated, as I have noted earlier i only know the basics of Microsoft SQL.

Thanks

PracticalProgram · #7 (**permalink**) 11-10-11, 16:40

I'm confused about line 2) . . . the table is named Conthist, but it is also named contact2?

Which is it?

Here's your query cleaned-up a bit, with some assumptions as to the associations of the databases with the tables:

Code:

SELECT	C1.*
FROM	Goldmine_MIOA..CONTACT1 C1 (NOLOCK)
JOIN	(
		SELECT
		DISTINCT
				ACCOUNTNO
		FROM	GoldMine_GMBase..CAL
		WHERE	CAL.ONDATE >= '20111111'
				AND CAL.ONDATE <= '20111111'
				AND CAL.RECTYPE='S'
		) CL ON
			CL.AccountNo = C1.AccountNo
JOIN	(
		SELECT
		DISTINCT
				ACCOUNTNO
		FROM	Goldmine_MIOA..CONTHIST
		WHERE	CONTHIST.ONDATE >= '20111111'
				AND CONTHIST.ONDATE <= '20111111'
				AND CONTHIST.REF LIKE '%Pharmacy Worksheet%'
		) CH ON
			CH.AccountNo = C1.AccountNo
ORDER
BY		C1.CONTACT

Those date criteria do not make any sense. If something is greater than or equal to November 11, 2011 AANNDD that same thing is also less than or equal to November 11, 2011, then those two conditions can only exist if that thing IS November 11, 2011.

Was that date just a placeholder?

ahmz · #8 (**permalink**) 11-10-11, 16:46

Hi Ken,

Thank you very much for your reply. I tried copying that query into sql and here are the errors i got back.

Server: Msg 107, Level 16, State 2, Line 1
The column prefix 'CAL' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 'CAL' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 'CAL' does not match with a table name or alias name used in the query.

In regards to conthist this is a totally seperate table within the Goldmine_MIOA database. From the Goldmine_MIOA database we are looking at 2 tables (contact1 and conthist) there is no CONTACT2 (SORRY) from the Goldmine_GMBase database we are looking at 1 (CAL)

I hope this makes sense

Thanks

Wim · #9 (**permalink**) 11-10-11, 17:04

Try this:

Code:

SELECT C1.* 
FROM Goldmine_MIOA.dbo.CONTACT1 AS C1 WITH (NOLOCK) 
	INNER JOIN (SELECT DISTINCT ACCOUNTNO 
		FROM GoldMine_GMBase.dbo.CAL as CAL
		WHERE CAL.ONDATE BETWEEN '20111111' AND '20111111' AND 
			CAL.RECTYPE in ('S')
		) CL ON 
		CL.AccountNo = C1.AccountNo 
	INNER JOIN (SELECT DISTINCT ACCOUNTNO 
		FROM Goldmine_MIOA.dbo.CONTHIST as CONTHIST
		WHERE CONTHIST.ONDATE BETWEEN '20111111' AND '20111111' AND 
			CONTHIST.REF LIKE '%Pharmacy Worksheet%'
		) CH ON 
		CH.AccountNo = C1.AccountNo 
ORDER BY C1.CONTACT

You may have to change "dbo" by the real schema names of your tables.

ahmz · #10 (**permalink**) 11-10-11, 17:09

Hi,

I tried this code but unfortunatley unsuccessful

here are the errors with this query

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '.'.
Server: Msg 170, Level 15, State 1, Line 7
Line 7: Incorrect syntax near 'CL'.
Server: Msg 170, Level 15, State 1, Line 13
Line 13: Incorrect syntax near 'CH'.

I have just confirmed and the database owner is "dbo" if this helps. I HATE SQL lol, Its very urgent i get this code running the next few hours

i have been working on it with minimal knowlege and some research since yesterday. I wish your awesome knowledge of SQL saves me from this misery!

ahmz · #11 (**permalink**) 11-10-11, 17:12

WAIT IT WORKED IT WORKED YOU ARE A LEGEND THE WHOLE CODE DIDNT COPY BEFORE YAYYYYYYY YOU ARE A GENIUS,

ONLY PROBLEM NOW is its returning no results

ahmz · #12 (**permalink**) 11-10-11, 17:22

Hi Sorry mate i figured out where the problem is

i need to query to look for records that does not contain '%Pharmacy%',

SELECT C1.*

FROM Goldmine_MIOA.dbo.CONTACT1 AS C1 WITH (NOLOCK)
INNER JOIN (SELECT DISTINCT ACCOUNTNO
FROM GoldMine_GMBase.dbo.CAL as CAL
WHERE CAL.ONDATE BETWEEN '20111111' AND '20111111' AND
CAL.RECTYPE in ('S')
) CL ON
CL.AccountNo = C1.AccountNo

INNER JOIN (SELECT DISTINCT ACCOUNTNO
FROM Goldmine_MIOA.dbo.CONTHIST as CONTHIST
WHERE CONTHIST.ONDATE BETWEEN '20111111' AND '20111111' AND
CONTHIST.REF LIKE '%Pharmacy%'
) CH ON
CH.AccountNo = C1.AccountNo

ORDER BY C1.CONTACT

Wim · #13 (**permalink**) 11-11-11, 02:35

Quote:

Originally Posted by ahmz

Hi Sorry mate i figured out where the problem is

i need to query to look for records that does not contain '%Pharmacy%',

Then your code should be :

Code:

SELECT C1.* 
FROM Goldmine_MIOA.dbo.CONTACT1 AS C1 WITH (NOLOCK) 
	INNER JOIN (SELECT DISTINCT ACCOUNTNO 
			FROM GoldMine_GMBase.dbo.CAL as CAL
			WHERE CAL.ONDATE BETWEEN '20111111' AND '20111111' AND 
				CAL.RECTYPE in ('S')
			) AS CL ON 
		CL.AccountNo = C1.AccountNo 
	INNER JOIN (SELECT DISTINCT ACCOUNTNO 
			FROM Goldmine_MIOA.dbo.CONTHIST as CONTHIST
			WHERE CONTHIST.ONDATE BETWEEN '20111111' AND '20111111' AND 
				CONTHIST.REF NOT LIKE '%Pharmacy%'
			) AS CH ON 
		CH.AccountNo = C1.AccountNo 
ORDER BY C1.CONTACT

Internet Services

Web Development

Digital Marketing

Consumer Tech