| Author |
 Topic  |
|
|
davidais
Starting Member
17 Posts |
 Posted - 10/04/2012 : 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 |
Edited by - davidais on 10/04/2012 10:27:48
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1420 Posts |
Posted - 10/04/2012 : 09:36:00
|
Please provide expected output
--
Chandu |
 |
|
|
davidais
Starting Member
17 Posts |
Posted - 10/04/2012 : 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
|
Edited by - davidais on 10/04/2012 09:46:25 |
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1420 Posts |
Posted - 10/04/2012 : 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 - 10/04/2012 : 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... |
Edited by - davidais on 10/04/2012 10:00:23 |
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1420 Posts |
Posted - 10/04/2012 : 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 - 10/04/2012 : 10:27:25
|
| Ahhhhh, whoops - cheers for that! |
|
|
| |
Topic |
|
*SOLVED* Joining two columns for two tables
