Creating a Junction Table - 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)

Creating a Junction Table

Author

Topic

Fred_Scuttle
Starting Member

13 Posts

Posted - 2005-11-04 : 03:54:43

Hi I am fairly new to TSQL and am studying for the 70-229 exam.

I am trying to create a view which will act as a junction table so that I can run queries on tables that have a many to many relationship.
I have Table1 and Table2

The link between both is a field called ACCOUNTNO
The Unique Field for my junction table is called RECID (By that I mean that every RECID in tables1 and Table2 is different, whereas the ACCOUNTNO field will appear many times in both tables)

Does this make sense? (or only to me?)

ANy way I want to create a view that shows me
two columns - RECID and ACCOUNTNO.
But shows me the stuff from both tables.
EXAMPLE

RECID ACCOUNTNO
1234 1001
2345 1001
3456 1002
4567 1002
5678 1002
6789 1003
7890 1004
0123 1004

Thanks

Fred

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-04 : 04:00:25

Post some sample data from two tables and the result you want

Madhivanan

Failing to plan is Planning to fail

Fred_Scuttle
Starting Member

13 Posts

Posted - 2005-11-04 : 05:02:25

Here is attached image of this requirement

Fred_Scuttle
Starting Member

13 Posts

Posted - 2005-11-04 : 05:07:27

OOPS - I thought it would attach the image.

Table1
ACCNO col1 col2 RECID
1001 data data H123
1001 data data H124
1001 data data H125
1005 data data H126
1110 data data H127
1001 data data H128

Table2
ACCNO col1 col2 RECID
1001 data data C001
1005 data data C002
1001 data data C003
1001 data data C004

Desired Result
RECID ACCOUNTNO
H123 1001
H124 1001
H125 1001
H126 1005
H127 1110
H128 1001
C001 1001
C002 1005
C003 1001
C004 1001

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-04 : 05:09:27

Try this

Select RECID,ACCNo from Table1
Union All
Select RECID,ACCNo from Table2

Madhivanan

Failing to plan is Planning to fail

Fred_Scuttle
Starting Member

13 Posts

Posted - 2005-11-04 : 06:00:48

Thanks that works.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources