SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 *SOLVED* Joining two columns for two tables
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

davidais
Starting Member

17 Posts

Posted - 10/04/2012 :  09:21:00  Show Profile  Reply with Quote
Hi all,

I need to link orderNo to projectNo on two tables
Between the two tables, this depends on the combination of both [name] and [state]
How would I link the two tables as linking either [name] or [state] results in duplicates? Table example:


table1
orderNo		name		state
________________________________________

order1		bob		stateX		
order2		bob		statey
order3		jim		stateX
order4		jim		stateY	


table2
name		state		projectNo
________________________________________

bob		stateX		project1
bob		statey		project2
jim		stateX		project3
jim		stateY		project4




SELECT
		orderNo, projectNo		
FROM
		table1
		LEFT JOIN table2 on table1.name=table1.name


Many thanks,

D

Edited by - davidais on 10/04/2012 10:27:48

bandi
Flowing Fount of Yak Knowledge

India
2206 Posts

Posted - 10/04/2012 :  09:36:00  Show Profile  Reply with Quote
Please provide expected output

--
Chandu
Go to Top of Page

davidais
Starting Member

17 Posts

Posted - 10/04/2012 :  09:43:33  Show Profile  Reply with Quote
This is what I want:

orderNo	projectNo
order1	project1
order2	project2
order3	project3
order4	project4


Currently this is what i get:

orderNo	projectNo
order1	project2
order1	project1
order2	project2
order2	project1
order3	project4
order3	project3
order4	project4
order4	project3

Edited by - davidais on 10/04/2012 09:46:25
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2206 Posts

Posted - 10/04/2012 :  09:50:52  Show Profile  Reply with Quote
DECLARE @table1 TABLE (orderNo varchar(20), name varchar(6), state varchar(10))
INSERT INTO @table1
SELECT 'order1', 'bob', 'stateX' UNION ALL
SELECT 'order2', 'bob', 'statey' UNION ALL
SELECT 'order3', 'jim', 'stateX' UNION ALL
SELECT 'order4', 'jim', 'stateY'

DECLARE @table2 TABLE (name varchar(20), state varchar(6), projectno varchar(10))
INSERT INTO @table2
SELECT 'bob', 'stateX', 'project1' UNION ALL
SELECT 'bob', 'statey', 'project2' UNION ALL
SELECT 'jim', 'stateX', 'project3' UNION ALL
SELECT 'jim', 'stateY', 'project4'


SELECT OrderNo, ProjectNo
FROM @table1 t1 JOIN @table2 t2
ON t1.name = t2.name AND t1.state = t2.state


--
Chandu
Go to Top of Page

davidais
Starting Member

17 Posts

Posted - 10/04/2012 :  09:54:31  Show Profile  Reply with Quote
Thanks but what would I do if I had two tables with thousands of records in each?

I come from a Access background and the migration is proving difficult, in Access I would have joined the both [name] and [state] with the resulting code.

LEFT JOIN table2 on table1.name=table1.name AND LEFT JOIN table2 on table1.state=table1.state

SQL doesn't seem to like the AND operator used with JOINS...

Edited by - davidais on 10/04/2012 10:00:23
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2206 Posts

Posted - 10/04/2012 :  10:02:35  Show Profile  Reply with Quote

Try

FROM table1 JOIN table2
ON table1.name = table2.name AND table1.state = table2.state

--
Chandu
Go to Top of Page

davidais
Starting Member

17 Posts

Posted - 10/04/2012 :  10:27:25  Show Profile  Reply with Quote
Ahhhhh, whoops - cheers for that!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000