How to Use Joined Tables When querying a LinkedServer

Question

I have a Linked Server LinkedServer1 I'd like to query using Joined tables.

My query example:

   SELECT     dds.Invoice_Tb.Store_Number, dds.Invoice_Tb.Invoice_Number,
                 dds.Invoice_Detail_Tb.Invoice_Number AS Invoice
      FROM      [LinkedServer1].Database1.dds.Invoice_Tb INNER JOIN
                [LinkedServer1].Database1.dds.Invoice_Detail_Tb ON 
                [LinkedServer1].Database1.dds.Invoice_Tb.Invoice_Number = 
                [LinkedServer1].Database1.dds.Invoice_Detail_Tb.Invoice_Number

This Query, when executed gives me an error:

The multi-part identifier could not be bound for each of the above table references.  It references each table and column listed above.

How do you properly query data using Table Joins with a linked server?

Answer 1

Use alias:

select  a.Store_Number,
        a.Invoice_Number,
        a.Invoice_Number as Invoice
from    [LinkedServer1].Database1.dds.Invoice_Tb as a
        inner join [LinkedServer1].Database1.dds.Invoice_Detail_Tb  as b on
           a.Invoice_Number = b.Invoice_Number