Inner Joining from other databases

Question

I need to inner join data from a different databases. Three to be exact.

I've tried to do a Select Statement from the different databases but I can't get the inner join to work for some reason.

Should I Inner join one at a time instead of doing all 3 at once? The tricky part has been assigning the results table from the first query to a variable so that I could inner join it with the subsequent tables.

Current Code I have

(DECLARE @SDate date = '01/01/2018'
DECLARE @EDate date = '12/31/2018'

DECLARE @Sku TABLE
(
  Sku varchar(20)
);

INSERT @Sku (Sku)
VALUES 
(N'SN2150'),
(N'SN2151'); 

SELECT        
   l.No_ 
  ,SUM(l.Amount) AS Amount
  ,SUM(l.Quantity) AS Quantity
FROM 
  dbo.[Threshold Enterprises$Sales Invoice Line] AS l
  JOIN
   @Sku AS s
    ON s.Sku = l.No_
WHERE
  l.[Shipment Date] BETWEEN @SDate AND @EDate
GROUP BY
  l.No_) 

  AS a


Select [Item.No_] and [Suggested Retail Price] and [Starting Date] FROM [NAV- 
   WH].dbo.v_NAV_PurchasePriceCurrent b

Select [No_] and [Size] and [Size.Type] FROM [NAV-WH].[dbo].[Threshold 
Enterprises$Item] c


Inner Join a b c where a.[@SKU]=b.[Item.No_]=c.[No_]

Get the following Error Message

Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'DECLARE'.
Msg 102, Level 15, State 1, Line 26
Incorrect syntax near ')'.

Expect the results to give me the SKU, sales, units sold, retail price, starting data, size, and size type.

Tried the previous code but it gave the same error message of

Msg 207, Level 16, State 1, Line 25
Invalid column name 'Item.No_'.

Code I had tried to use to instead spell everything out was:

DECLARE @SDate date = '01/01/2018'
DECLARE @EDate date = '12/31/2018'

DECLARE @Sku TABLE
(
  Sku varchar(20)
);

INSERT @Sku (Sku)
VALUES 
(N'SN2150'),
(N'SN2151'); 

SELECT        
   l.No_ as SKU
  ,SUM(l.Amount) AS Amount
  ,SUM(l.Quantity) AS Quantity
  ,[NAV-WH].[dbo].[v_NAV_PurchasePriceCurrent].[Suggested Retail Price]
  ,[NAV-WH].[dbo].[v_NAV_PurchasePriceCurrent].[Starting Date]
  ,[NAV-WH].[dbo].[ThresholdEnterprises$Item].Size
  ,[NAV-WH].[dbo].[ThresholdEnterprises$Item].[Size.Type]
FROM 
  dbo.[Threshold Enterprises$Sales Invoice Line] AS l
  JOIN @Sku AS s ON s.Sku = l.No_
  JOIN [NAV-WH].[dbo].[v_NAV_PurchasePriceCurrent] on [NAV-WH].[dbo]. 
   [v_NAV_PurchasePriceCurrent].[Item.No_] = l.No_
  JOIN [NAV-WH].[dbo].[ThresholdEnterprises$Item] on [NAV-WH].[dbo]. 
   [ThresholdEnterprises$Item].[No_] = l.No_
WHERE
  l.[Shipment Date] BETWEEN @SDate AND @EDate
GROUP BY
  l.No_

What happened when you tried? You haven't told us what the problem is? An error message or incorrect data, etc? — Wes H, Jun 26 '19 at 16:48
We'd prefer not to guess what the syntax errors are. Can you post them as well? Also, show us the statement that generates the syntax errors. — Wes H, Jun 26 '19 at 16:52
Update your question with the error message and the whole statement that caused the error message. — Wes H, Jun 26 '19 at 16:55
Just updated the comment with the current code I had and error message. Sorry about not being clear the first time. — adura826, Jun 26 '19 at 17:15
Do you really have a column named "Item.No_"? Using special characters, such as [-] [$] [.] in object names is a terrible idea. If that is not just a typo and you are really using those characters, you need to get used to troubleshooting these errors as you will run into them a lot more. For now, please verify that you've typed the column name correctly in your join to "v_NAV_PurchasePriceCurrent" — Wes H, Jun 26 '19 at 18:31
I double checked the columns and turns out the spelling and spacing was causing the issue. However now it's saying NAV WH.dbo.v_NAV_PurchasePriceCurrent.Suggested Retail Price Is invalid because it's not contained in either an aggregate function or the GROUP BY clause. — adura826, Jun 26 '19 at 18:52
Please in code questions give a [mre]--cut & paste & runnable code; example input with desired & actual output (including verbatim error messages); tags & clear specification & explanation. That includes the least code you can give that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) For SQL that includes DBMS/product & DDL which includes constraints & indexes & base table initialization. PS You have a syntax error. Read the grammar. Show as much as possible that constituent subexpressions are OK. PS Please ask a new question in a new post. — philipxy, Jun 26 '19 at 18:54
Re your group by problem--read the manual about group by. You can't select a column that is multiple-valued per group, ie per subrow of values of group by columns. (Obviously--) That is a faq. Before considering posting please always google any error message & many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names; read many answers. If you post a question, use one phrasing as title. See [ask] & the voting arrow mouseover texts. — philipxy, Jun 26 '19 at 18:56
Possible duplicate of [Error related to only\_full\_group\_by when executing a query in MySql](https://stackoverflow.com/questions/34115174/error-related-to-only-full-group-by-when-executing-a-query-in-mysql) — philipxy, Jul 02 '19 at 17:23

Scott Hoffman · Answer 1 · 2019-06-26T17:56:32.657

If all the databases are on the same instance, use the 3-part name to reference them in the join statements.

FROM [DB1].[dbo].[TableA] a
JOIN [DB2].[dbo].[TableB] b on a.Key = b.Key
LEFT JOIN [DB3].[dbo].[TableC] on c.Key = a.Key

If the databases are on different instances, set up a linked server between them and then use the 4-part names ...JOIN [LINKEDSERVER].[DB2].[dbo].[TableB]...

This assumes the user executing the query has the appropriate permissions in all databases.

Edit - based on your edit to the original question, I would recommend doing it all at once. Try this (I couldn't test it because I don't have all your tables).

DECLARE @SDate date = '01/01/2018'
DECLARE @EDate date = '12/31/2018'

DECLARE @Sku TABLE
(
  Sku varchar(20)
);

INSERT @Sku (Sku)
VALUES 
(N'SN2150'),
(N'SN2151'); 

SELECT        
   l.No_ as SKU
  ,SUM(l.Amount) AS Amount
  ,SUM(l.Quantity) AS Quantity
  ,b.[Suggested Retail Price]
  ,b.[Starting Date]
  ,c.Size
  ,c.[Size.Type]
FROM 
  dbo.[Threshold Enterprises$Sales Invoice Line] AS l
  JOIN @Sku AS s ON s.Sku = l.No_
  JOIN [NAV-WH].[dbo].[v_NAV_PurchasePriceCurrent] b on b.[Item.No_] = l.No_
  JOIN [NAV-WH].[dbo].[ThresholdEnterprises$Item] c on c.[No_] = l.No_
WHERE
  l.[Shipment Date] BETWEEN @SDate AND @EDate
GROUP BY
  l.No_

Good Idea, I could do that with the bottom 2 databases but how do I declare the top query so that I could reference it? I've tried putting it into brackets but that isn't working for some reason — adura826, Jun 26 '19 at 16:58
@adura826 Just keep the top query how it is and use the 3-part name syntax to join the other 2 tables. As others have said, please show us what you have now and what error message you're still getting. — Scott Hoffman, Jun 26 '19 at 17:08
Yeah for some reason declaring the databases as b and c are coming up as errors, It's not recognizing the columns. I tried taking out the letters and spelling everything out but it still wouldn't go. I'll post what i tried. — adura826, Jun 26 '19 at 18:15

score 0 · Accepted Answer · answered Jul 02 '19 at 17:08

I was able to get it to work. It was a simple Group By work around. Thanks to those who pointed me in the right direction!

use [NAV-WH]

DECLARE @SDate date = '01/01/2018'
DECLARE @EDate date = '12/31/2018'

DECLARE @Sku TABLE
(
  Sku varchar(20)
);

INSERT @Sku (Sku)
VALUES 
(N'SN2150'),
(N'SN2151'); 


SELECT [Threshold Enterprises$Item].No_ AS SKU, SUM([Threshold 
Enterprises$Sales Invoice Line].Amount) AS Amount, SUM([Threshold 
Enterprises$Sales Invoice Line].Quantity) AS Quantity, 
                         v_NAV_PurchasePriceCurrent.[Suggested Retail Price], 
v_NAV_PurchasePriceCurrent.[Starting Date], [Threshold Enterprises$Item].Size, 
[Threshold Enterprises$Item].[Size Type]
FROM [Threshold Enterprises$Item] INNER JOIN
                         v_NAV_PurchasePriceCurrent ON [Threshold 
Enterprises$Item].No_ = v_NAV_PurchasePriceCurrent.[Item No_] INNER JOIN
                         [Threshold Enterprises$Sales Invoice Line] ON 
[Threshold Enterprises$Item].No_ = [Threshold Enterprises$Sales Invoice 
Line].No_
WHERE ([Threshold Enterprises$Item].No_ = N'SN2051' OR
                         [Threshold Enterprises$Item].No_ = N'SN2151') AND 
([Threshold Enterprises$Sales Invoice Line].[Shipment Date] BETWEEN @SDate AND 
@EDate)
GROUP BY [Threshold Enterprises$Item].No_, v_NAV_PurchasePriceCurrent. 
   [Suggested Retail Price], v_NAV_PurchasePriceCurrent.[Starting Date], [Threshold 
Enterprises$Item].Size, [Threshold Enterprises$Item].[Size Type]

Inner Joining from other databases

2 Answers2