Lookup from one table to other master table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Lookup from one table to other master table

Author

Topic

hky404
Starting Member

2 Posts

Posted - 2015-04-21 : 14:17:30

Hi there,
I am new to this forum, so I would like to apologize beforehand if I violate the forum policies in anyway.

I imported an Excel file in the form of a table(11,000 rows) in SQL Server 2012 database called Sanbox, I am trying to link this table to MS-CRM leads table so that I can link all the street addresses from table in Sandbox DB to the respective tables in MS-CRM leads table (200,000 rows), my end goal is to link the owner names from MS-CRM table to Sandbox DB's table with the matching criteria of 'street address'

My simple query looks like this, but it returns 200,000 rows, but I only want 11,000 rows -

SELECT * 
FROM Sandbox.dbo.Sheet1$ A
LEFT JOIN [IPlus_MSCRM].[dbo].[FilteredLead] B
ON A.[Street 1] = B.address1_line1

Can someone please explain to me what's wrong with this?

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-04-21 : 14:29:03

left join will also return non-matching rows. Try INNER JOIN

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2015-04-21 : 14:29:05

is it possible the address of the 200000 are all contained in the 11000 set- because for every record in the sheet1 set you will get all matching addresses in the CRM set. so if addresses are reused - you will get more than 11k

run the following
SELECT A.[Street 1], COUNT(B.address1_line1)
FROM Sandbox.dbo.Sheet1$ A
LEFT JOIN [IPlus_MSCRM].[dbo].[FilteredLead] B
ON A.[Street 1] = B.address1_line1
GROUP BY A.[Street 1],

hky404
Starting Member

2 Posts

Posted - 2015-04-21 : 14:55:56

quote:
Originally posted by gbritton

left join will also return non-matching rows. Try INNER JOIN

What do you think of this? But is's giving me an error that "multip-part identifier [IPlus_MSCRM].[dbo].[FilteredLead].owneridyominame could not be bound"

SELECT *, [IPlus_MSCRM].[dbo].[FilteredLead].owneridyominame
FROM Sandbox.dbo.Sheet1$ A
WHERE EXISTS 
(	
	SELECT *
	FROM [IPlus_MSCRM].[dbo].[FilteredLead] B
	WHERE A.[Street 1] = B.address1_line1
)

Kristen
Test

22859 Posts

Posted - 2015-04-22 : 07:05:26

quote:
Originally posted by hky404

giving me an error that "multip-part identifier [IPlus_MSCRM].[dbo].[FilteredLead].owneridyominame could not be bound"

SELECT *, [IPlus_MSCRM].[dbo].[FilteredLead].owneridyominame
FROM Sandbox.dbo.Sheet1$ A
You need to JOIN [IPlus_MSCRM].[dbo].[FilteredLead] here
WHERE EXISTS
(
SELECT *
FROM [IPlus_MSCRM].[dbo].[FilteredLead] B
WHERE A.[Street 1] = B.address1_line1
)

[IPlus_MSCRM].[dbo].[FilteredLead].owneridyominame is not in scope in the SELECT clause.

if you add a JOIN and an Alias Name (such as [B] that you used before, then you need to use that Alias Name in your SELECT (etc.) clause and NOT the full Database/Schema/Table name.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources