Using the IN clause to query two databases

[ajclifford]

Hello,

I've put together the following query, but it has been unsuccessful running it so far.

select * from Contractors.dbo.Contacts 
where VendorNo in (select No_ from [BMIS Live Database].dbo.[BMIS Live Database$Vendor] where [Name] like '%of%')

I'm trying to grab values from the column, "No_" in the Bmis.. database then only select data from the Contractors database if the VendorNo column holds one of the values grabbed from the Bmis.. database. I think my syntax is just completely wrong but hopefully someone might pick up on my mistakes. Any help would be greatly appreciated, thanks!

Alex

 

[SonOfEmidec1100]

Close, this should work

select * from Contractors.dbo.Contacts 
where VendorNo in (select No_ from [BMIS Live Database].dbo.Vendor where [Name] like '%of%')

Although I would probably use an INNER JOIN in this case

Select C.* FROM Contractors..Contacts C INNER JOIN [BMIS Live Database]..Vendor V
  ON C.VendorNo = V.No_ WHERE V.[Name] like '%of%'

 

[ajclifford]

Hi SonOfEmidec1100,

Thanks for the very fast reply!

I tried both examples you gave me but there are still errors. Just so you know, "BMIS Live Database$Vendor" is the actual table name.

The second example you gave me gives the same error as the others:

Server: Msg 446, Level 16, State 9, Line 1
Cannot resolve collation conflict for equal to operation.

I hope I've made this more clear now, thanks again!

Alex

 

[SonOfEmidec1100]

Ah sorry about the confusion thats a different issue, the collation sequence is different between the databases for one or more char/varchar fields reference in the query.

Its quite possible the database defaults are different, usual problem - check database properties, collation name.

What you will need to do is force them to be the same in the query, something like

where [Name] like '%of%' COLLATE <insert other collation name here>

For more info search BOL for collation conflict

 

[ajclifford]

Cool thanks SonOfEmidec1100, it works! The following query is what I ended up with:

select * from Contractors.dbo.Contacts 
where VendorNo collate SQL_Latin1_General_CP850_CI_AI in (select No_ from [BMIS Live Database].dbo.[BMIS Live Database$Vendor] 
where [Name] like '%off%')

Just out of curiosity, does it have to be the outter contractors select clause that had the collate attached to it or could I have put it on the inner bmis select?

Also whats "BOL"? I'ld like to read more on collation of ms sql databases. Thanks a ton for the quick help again SonOfEmidec1100, much appreicated.

Alex

 

[SonOfEmidec1100]

I think you can change either of them, as long as they are same.

BOL is the common term for Books OnLine which is the online help for SQL server, it should be installed when you install SQL server, or SQL server client tools.

[blue]Start/Microsoft SQL Server/Books OnLine[/blue]

I still think you would be better of (performance wise)with the inner join option if the sub select can return multiple rows.

 

[ajclifford]

Ok cool thanks again SonOfEmidec1100, I'll have a try with both.

This can be closed.

Alex