JOINING 2 DATABASE - 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 2005 Forums

Transact-SQL (2005)

JOINING 2 DATABASE

Author

Topic

lovinguy
Starting Member

12 Posts

Posted - 2009-06-24 : 13:28:28

I WOULD LIKE TO JOIN 2 DATABASE AND RETRIVE THE QUERY.

DATABASE 1 - POS ------ TABLE NAME - SALES

DATABASE 2 - CARD ------ TABLE NAME - DISCOUNT CARD

IN SALES TABLE DISCOUNT CARD NO. IS THERE BUT CARD HOLDER NAME IS PRESENT IN TABLE DISCOUNT CARD.

I WANT TO JOIN THE 2 TABLES WHO ARE ON DIFFERENT DATABASE AND RETRIVE THE INFORMATION.

THANKS IN ADVANCE.

LOVINGUY

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-24 : 13:37:56

just use


SELECT *
FROM POS..SALES s
JOIN CARD..[DISCOUNT CARD]dc
ON dc.DISCOUNTCARDNO=s.DISCOUNTCARDNO

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-24 : 13:44:53

i assume both the dbs are on same server

lovinguy
Starting Member

12 Posts

Posted - 2009-06-26 : 02:44:07

Thanks Visakh but i am unable to get the result of it.I will cleart the table structure

DATABASE1 NAME= POS ---- TABLE NAME=SALES ------ COMMON COLUMN=CARD NO.
DATABSE2 NAME= CARD ----- TABLE NAME=DISCOUNT CARD ----- COMMON COLUMN=CARD NO.

Please wrtie the full query.You are right both databases are on same server.

WAiting for your reply

lovinguy
Starting Member

12 Posts

Posted - 2009-06-26 : 05:01:45

Dear Members i am waiting for your prompt reply.

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-06-26 : 05:05:59

Hi, Try this once

select s.salesid,d.cardno from pos.dbo.sales s
inner join card.dbo.[disount card] as d on d.cardno = s.cardno

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-06-26 : 05:09:52

seq should be like this
servername.databasename.schemaname.tablename

lovinguy
Starting Member

12 Posts

Posted - 2009-06-26 : 13:52:08

Dear Nageswar I am getting this error message after running th query statement which you have advised me.

"Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation"

Waiting for your reply.Please others members also help me to run this query.

Thanks in advance.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-26 : 13:56:35

this means both table columns have diferent collation setting. anyways try below:-


SELECT *
FROM POS..SALES s
JOIN CARD..[DISCOUNT CARD]dc
ON dc.DISCOUNTCARDNO COLLATE SQL_Latin1_General_CP1_CI_AS=s.DISCOUNTCARDNO COLLATE SQL_Latin1_General_CP1_CI_AS

lovinguy
Starting Member

12 Posts

Posted - 2009-06-26 : 14:03:58

Dear Visakh! I tried to run this below query :-

select s.document_number,l.loyalty_name from gc.dbo.sales_header s
inner join loyalty.dbo.[loyalty] as l on l.loyalty_code=s.loyalty_code
COLLATE SQL_Latin1_General_CP1_CI_AS=s.loyalty_code COLLATE SQL_Latin1_General_CP1_CI_AS

ERROR MESSAGE

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '='.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-26 : 14:06:19

quote:
Originally posted by lovinguy

Dear Visakh! I tried to run this below query :-

select s.document_number,l.loyalty_name from gc.dbo.sales_header s
inner join loyalty.dbo.[loyalty] as l on l.loyalty_code COLLATE SQL_Latin1_General_CP1_CI_AS=s.loyalty_code
COLLATE SQL_Latin1_General_CP1_CI_AS~~=s.loyalty_code COLLATE SQL_Latin1_General_CP1_CI_AS~~

ERROR MESSAGE

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '='.

modify like above

lovinguy
Starting Member

12 Posts

Posted - 2009-06-26 : 14:18:58

Thanks Visakh!!!!!!! Its working.

If i want to add more filter or specify date range for data retrieval like

DATE>='2009/05/01' and date<='2009/05/31'

Thanks once again Visakh.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-26 : 14:25:14

cant you make a try at least by modifying the one which i provided? clue is where clause is used in sql for filtering. make a try and i'll help you if its not working

lovinguy
Starting Member

12 Posts

Posted - 2009-06-26 : 14:41:12

THANKS VISAKH.ACTUALLY THIS QUERY IS NEW FOR ME SO I THOUGH BETTER TO ASK RATHER THAN STRUGGLING OR SOME OTHER MEMBER CAN GIVE HIS/HER OWN IDEA.ANYWAYS THANKS MATE.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-26 : 14:47:57

[code]
select s.document_number,l.loyalty_name from gc.dbo.sales_header s
inner join loyalty.dbo.[loyalty] as l on l.loyalty_code COLLATE SQL_Latin1_General_CP1_CI_AS=s.loyalty_code
COLLATE SQL_Latin1_General_CP1_CI_AS
WHERE DATE>='2009/05/01' and date<='2009/05/31'
[/code]

lovinguy
Starting Member

12 Posts

Posted - 2009-06-26 : 14:50:13

Thanks Visakh! I tried earlier and it working fine.I tried to use where clause before the "COLLATE SQL_Latin1_General_CP1_CI_AS"

Its working.Thanks for your help and effort.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-26 : 14:52:36

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources