Results 1 to 13 of 13
  1. #1
    Join Date
    Nov 2011
    Posts
    7

    Red face Unanswered: Query & join between 2 databases on same server

    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

  2. #2
    Join Date
    Feb 2004
    Location
    Bangalore, India
    Posts
    250
    Select GMC.*, GMB.*
    From Goldmine_MIOA..Contact1 GMC
    Inner Join Goldmine_GMBase..Cal GMB
    On <use appropriate join condition here>
    Last edited by baburajv; 11-10-11 at 05:59.
    Cheers....

    baburajv

  3. #3
    Join Date
    Nov 2011
    Posts
    7

    Sql query

    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

  4. #4
    Join Date
    Feb 2004
    Location
    Bangalore, India
    Posts
    250
    Pls refer Using Inner Joins


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

    baburajv

  5. #5
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Quote Originally Posted by ahmz View Post
    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.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  6. #6
    Join Date
    Nov 2011
    Posts
    7

    Sql query

    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

  7. #7
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    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?
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  8. #8
    Join Date
    Nov 2011
    Posts
    7

    Sql query

    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
    Last edited by ahmz; 11-10-11 at 17:53.

  9. #9
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    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.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  10. #10
    Join Date
    Nov 2011
    Posts
    7

    Sql query

    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!

  11. #11
    Join Date
    Nov 2011
    Posts
    7

    Sql query

    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

  12. #12
    Join Date
    Nov 2011
    Posts
    7

    Sql query

    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

  13. #13
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    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
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •