One-To-Many Join - 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 2000 Forums

Transact-SQL (2000)

One-To-Many Join

Author

Topic

jportnoy
Starting Member

7 Posts

Posted - 2006-08-14 : 12:12:18

I have the following setup:

Table A______________Table B
Name...Value____NameID____Value
Name1...x_________x_______"John"
Name2...y_________y_______"Frank"

I need to create a join that would allow me to create a view matching up the value of table A with the ID of table B. Can somebody send me a code snippet on how I would do this with a join? Thank you.

nr
SQLTeam MVY

12543 Posts

Posted - 2006-08-14 : 12:16:43

not sure what you want but it looks like

select *
from TableA a
join TableB b
on a.Value = b.NameID

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jportnoy
Starting Member

7 Posts

Posted - 2006-08-14 : 12:21:55

Alright, I wasn't nearly clear enough.

Table A: contains a field "Name" and another field "Name2". The data types for these fields are ints.

Table B: contains two fields: "NameID" and "ActualName".

The fields "Name" and "Name2" from Table A need to match up with the "ActualName" from Table B.

I need to create a view that has fields "Name1" and "Name2" that contain the value of the "ActualName" field in Table B.

Sorry for the lack of clarity in the last post.

nr
SQLTeam MVY

12543 Posts

Posted - 2006-08-14 : 12:54:29

select Name1 = b1.ActualName ,
Name2 = b2.ActualName
from TableA a
join TableB b1
on a.Name = b1.NameID
join TableB b2
on a.Name2= b2.NameID

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jportnoy
Starting Member

7 Posts

Posted - 2006-08-14 : 13:53:22

Is there a way to get one record back, instead of the 2 that are currently being returned? (They're identical records with the actualName field changed)

nr
SQLTeam MVY

12543 Posts

Posted - 2006-08-14 : 14:08:54

It will bring back one row for each row in TableA.
Maybe you have the reverse entry in TableA?
if so
select Name1 = b1.ActualName ,
Name2 = b2.ActualName
from TableA a
join TableB b1
on a.Name = b1.NameID
join TableB b2
on a.Name2= b2.NameID
where a.Name <= a.Name2

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jportnoy
Starting Member

7 Posts

Posted - 2006-08-14 : 15:17:24

Awesome. Thanks a bunch man.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources