CODEWITHSUNDEEP
sql-server
Emma
Emma

Reputation: 23

How to use Join on linked servers

I am trying to get results of a customer from two linked servers remotely. i need to sum the points of every cust_id but am having problems with my query

SELECT  sum(cust_point) as total
FROM [192.168.23.9].[POSDBV4].[dbo].[loyal_summery_branch] where cust_id='0100015388' 
INNER JOIN [192.168.13.4].[POSDBV4].[dbo].[loyal_summery_branch]
ON cust_id.[192.168.23.9].[POSDBV4].[dbo].[loyal_summery_branch]=cust_id.[192.168.13.4].[POSDBV4].[dbo].[loyal_summery_branch];

Upvotes: 0

Views: 55

Answers (3)

JamieD77
JamieD77

Reputation: 13949

you can always use a UNION ALL here if you like.. this will allow you select other fields as well if you include a GROUP BY

SELECT  SUM(cust_point) AS total
FROM    (
            SELECT  cust_point
            FROM    [192.168.23.9].[POSDBV4].[dbo].[loyal_summery_branch]
            WHERE   cust_id = '0100015388'
            UNION ALL
            SELECT  cust_point
            FROM    [192.168.13.4].[POSDBV4].[dbo].[loyal_summery_branch]
            WHERE   cust_id = '0100015388'
        ) t

Upvotes: 0

Ashish Rajput
Ashish Rajput

Reputation: 1529

As you want the sum of cust_point of both of the table. Please find the query below

Select( (SELECT  sum(cust_point)
        FROM [192.168.23.9].[POSDBV4].[dbo].[loyal_summery_branch] where cust_id='0100015388') +  
    (SELECT  sum(cust_point)
        FROM [192.168.13.4].[POSDBV4].[dbo].[loyal_summery_branch] where cust_id='0100015388') ) as total

Upvotes: 1

mikeagg
mikeagg

Reputation: 751

I think you have your query syntax a little scrambled there. Try this.

SELECT sum(cust_point) as total 
FROM [192.168.23.9].[POSDBV4].[dbo].[loyal_summery_branch] A
INNER JOIN [192.168.13.4].[POSDBV4].[dbo].[loyal_summery_branch] B ON A.cust_id=B.cust_id
WHERE cust_id='0100015388'

Upvotes: 1

Related Questions