CODEWITHSUNDEEP
sqlsql-serversql-server-2008
euge1220
euge1220

Reputation: 235

SQL Joining Values to A Set Of Values

Say I have two tables:
Table 1: 

number
1
2
3

and Table 2: 

letter
a
b
c
d
e
f
g

how can i produce

number      letter
a              1
a              2
a              3
b              1
b              2
b              3
c              1
c              2
c              3

all the way to g - i would just like to assign one of each value to each specific value of a table

Upvotes: 2

Views: 122

Answers (3)

Nenad Zivkovic
Nenad Zivkovic

Reputation: 18559

While the query suggested like:

SELECT number, letter FROM Table1, Table2

will work the same, and even might look simpler - DON'T USE IT. It is an old style syntax outdated more then 20 years ago with the introduction of ANSI-92 standards.

Please use ANSI-JOINS:

SELECT t1.number, t2.letter
FROM Table1 t1
CROSS JOIN Table2 t2

Also get in the habit to always alias and prefix your columns.

Upvotes: 12

BWS
BWS

Reputation: 3836

to get what you're looking for, do this:

select letter,number 
from Table2 cross join Table1 
order by letter,number

Upvotes: -1

Icarus
Icarus

Reputation: 63956

Cross join:

select Table1.number, Table2.letter 
from Table1 CROSS JOIN Table2

Upvotes: 5

Related Questions