Creating 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

General SQL Server Forums

New to SQL Server Programming

Creating join

Author

Topic

Gina
Starting Member

4 Posts

Posted - 2012-01-24 : 18:33:04

Hi, Hoping someone can help please:

I have two tables, one with thousands of job numbers and the other with 8 accounts I need to assign to each job number, here is sample of the tables:
Table 1 Table 2
JobNumber Account
1023 5340
2251 5355
3123 5356
4258 etc 5357 etc

In a new table I would like each job to look as follows:
1023.5340
1023.5355
1023.5356
1023.5357
2251.5340
2251.5355
2251.5356
2251.5357 etc etc..

I am stumped as to how I go about writing this, can anyone help please as I am quite new to this...
Thanks,
Gina

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-01-24 : 18:44:17

If all you want is for each Job Number to be associated with every possible account, then you need to CROSS JOIN them

Select JobNumber,Account
From table1
Cross Join table2

Jim

Everyday I learn something that somebody else already knew

Gina
Starting Member

4 Posts

Posted - 2012-01-24 : 19:01:38

You're a GEM!
That's too easy, thanks so much, I really appreciate it!

Gina
Starting Member

4 Posts

Posted - 2012-01-24 : 20:37:30

Another question how would I then write this as an update Statement please:

Select JobNumber,Account
From table1
Cross Join table2

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-01-24 : 22:15:10

quote:
Originally posted by Gina

Another question how would I then write this as an update Statement please:

Select JobNumber,Account
From table1
Cross Join table2

what do you want to update ? which table ? which column ? update with what ?

KH
[spoiler]Time is always against us[/spoiler]

Gina
Starting Member

4 Posts

Posted - 2012-01-24 : 23:42:13

in a new table(table 3) I would like to have the following in a column:
New Job Number
1023.5340
1023.5355
1023.5356
1023.5357
2251.5340
2251.5355
2251.5356

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-01-25 : 00:52:33

[code]
insert into table3 (JobNumber, Account)
Select JobNumber,Account
From table1
Cross Join table2
[/code]

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources