Need Help Joining - SQL Server Forums

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

SQL Server 2008 Forums

Transact-SQL (2008)

Need Help Joining

Author

Topic

ross_gt
Starting Member

23 Posts

Posted - 2011-06-09 : 13:48:31

Hi I'm trying to create one table that will show our distinct customers in Canada in 2011. I have one table called X that has Ship To Customer ID, Country, and Year. The other table has Customers and S#. S# and Ship To Customer ID must be set equal. How do I join these two tables to get the desired out come mentioned above?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-06-09 : 13:51:52

It is really not so simple to understand what you need.
Can you please describe what you want in a way like shown in the picture below?

No, you're never too old to Yak'n'Roll if you're too young to die.

ross_gt
Starting Member

23 Posts

Posted - 2011-06-09 : 13:58:47

Something like this:

2011 Customers Country Year
X CA 2011

ross_gt
Starting Member

23 Posts

Posted - 2011-06-09 : 13:59:39

Sorry the formatting got messed up. Imagine those are three columns.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-09 : 14:20:25

If you just want customer ids, you may not even need to join with the Customers table. Something like this will giv eyou the customer ids

SELECT DISTINCT
	CustomerId
FROM
	X
WHERE
	country = 'CA'
	AND [YEAR] = 2011

If you do need some information that is only in the customers table, for example, first name and last name, one way to do it would be this:

SELECT DISTINCT
	x.CustomerId,
	c.LastName,
	c.FirstName
FROM
	Customers C
	INNER JOIN X ON x.CustomerId = c.[S#]
WHERE
	x.country = 'CA'
	AND x.[YEAR] = 2011

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources