Link to home
Create AccountLog in
Avatar of ammartahir1978
ammartahir1978Flag for United Kingdom of Great Britain and Northern Ireland

asked on

how to get information from different sql tables

Hi Guys,

I have a query which i want to join with a table in a different database to get COST price, how can i do that. query below:

select
pos Aud_receipt_no as RECECIPT_NO,
Aud_prod_code as PRODUCT_CODE,
Aud_EAN as EAN,
Aud_desc as DESCRIPTIONS,
Aud_quantity as QUANTITY,
Aud_value as SOLD_VALUE,
Aud_VAT as VAT,
Aud_weight as WEIGHT,
Aud_short_desc as PRODUCT_SHORT_DESC,
Aud_Date_time as TRANS_DATE_TIME,
Aud_trans_type as TRANSACTION_TYPE
--ud_trans_desc
 

from _audit
WHERE AUD_PROD_CODE != 'null'
Avatar of Éric Moreau
Éric Moreau
Flag of Canada image

select *
from _audit as A
left join othertable as OT
on ot.fk = a.k
I've made some assumptions in the query below, as there is information missing from the question regarding the "other table."

Assumptions:
- Other database is on the same server
- Inner join the two tables using the product code (i.e. product code is the primary key in "other table")

If the query below works for you, you will have to replace all of the object names with the correct ones.

select
A.pos Aud_receipt_no as RECECIPT_NO,
A.Aud_prod_code as PRODUCT_CODE,
A.Aud_EAN as EAN,
A.Aud_desc as DESCRIPTIONS,
A.Aud_quantity as QUANTITY,
A.Aud_value as SOLD_VALUE,
A.Aud_VAT as VAT,
A.Aud_weight as WEIGHT,
A.Aud_short_desc as PRODUCT_SHORT_DESC,
A.Aud_Date_time as TRANS_DATE_TIME,
A.Aud_trans_type as TRANSACTION_TYPE,
B.ProductCostPrice
--ud_trans_desc
 

from _audit AS A
INNER JOIN OtherDatabaseName.SchemaName.ProductCostTable AS B
ON A.Aud_prod_code = B.ProductCode
WHERE A.AUD_PROD_CODE != 'null'
Avatar of ammartahir1978

ASKER

hi Thank you
the table i am trying to connect is different sql instance.

select
Aud_receipt_no as RECECIPT_NO,
Aud_prod_code as PRODUCT_CODE,
Aud_EAN as EAN,
Aud_desc as DESCRIPTIONS,
Aud_quantity as QUANTITY,
Aud_value as SOLD_VALUE,
Aud_VAT as VAT,
Aud_weight as WEIGHT,
Aud_short_desc as PRODUCT_SHORT_DESC,
Aud_Date_time as TRANS_DATE_TIME,
Aud_trans_type as TRANSACTION_TYPE
--ud_trans_desc
 

from [eretail1-1].[pos audit]_audit
inner joint uks-cafe1-1.street.brprod as B
on a.aud_prod_code = b.i_prod_code

WHERE AUD_PROD_CODE != 'null'

will this work?
try it!

if your credentials are also valid for the second database, it should work.

another way of doing it would be to link the 2 servers.
this is what i get

Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'eretail1-1.pos audit._audit'.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'uks-cafe1-1.street.brprod'.
In order to query a different instance, the servers have to be linked.

When you specify a source in a FROM clause, you will need:

[Server\instance].[Database].[Schema].[TableName]

So your FROM appears to be missing some information. Or you are missing some dots.

Also you have a typo: Inner Joint should be Inner Join
ASKER CERTIFIED SOLUTION
Avatar of Simone B
Simone B
Flag of Canada image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
I did that and get this error:

Server: Msg 7202, Level 11, State 2, Line 1
Could not find server 'uks1-1' in sysservers. Execute sp_addlinkedserver to add the server to sysservers.

but this server is the main SQL server. how can i resolve it?
i am receiving this error now

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'SQLOLEDB' reported an error. Authentication failed.
[OLE/DB provider returned message: Invalid authorization specification]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IDBInitialize::Initialize returned 0x80040e4d:  Authentication failed.].
okay i have resolved the linked server issue but now i get this

Server: Msg 446, Level 16, State 9, Line 1
Cannot resolve collation conflict for equal to operation.
excellent
I'm sorry, I didn't see your comments over the weekend. I'm glad it worked out for you!