*SOLVED* Joining two columns for two tables

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

*SOLVED* Joining two columns for two tables

Author

Topic

davidais
Starting Member

17 Posts

Posted - 2012-10-04 : 09:21:00

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

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-10-04 : 09:36:00

Please provide expected output

--
Chandu

davidais
Starting Member

17 Posts

Posted - 2012-10-04 : 09:43:33

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

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-10-04 : 09:50:52

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

davidais
Starting Member

17 Posts

Posted - 2012-10-04 : 09:54:31

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...

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-10-04 : 10:02:35

Try

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

--
Chandu

davidais
Starting Member

17 Posts

Posted - 2012-10-04 : 10:27:25

Ahhhhh, whoops - cheers for that!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources