CODEWITHSUNDEEP
sql-server-2005t-sqljoin
FriskyKitty
FriskyKitty

Reputation: 53

Joining unrelated tables

I am working in SQL Server 2005. I have a table that lists stores and a separate table that lists departments. There is no key relationships between the tables. My goal is create a query that lists each department for each store. Ideally the results will be:

store  department
1        candy
1        ice
1        drinks
2        candy
2        ice
2        drinks

Upvotes: 1

Views: 1364

Answers (5)

Vinny Roe
Vinny Roe

Reputation: 901

Is this the same as a cartesian?

SELECT s.store, d.department
FROM stores s, departments d

Upvotes: 0

EricZ
EricZ

Reputation: 6205

You can use JOIN too

SELECT s.store, d.department
FROM stores s
JOIN departments d
  ON 1=1

Upvotes: 0

Joe
Joe

Reputation: 42607

You can do the above query with a cross join (with no relationships in the WHERE clause)

SELECT d.department, s.store FROM departments d
CROSS JOIN stores s

Upvotes: 7

Ry-
Ry-

Reputation: 224905

Like this: http://ideone.com/KErj3

Use JOIN. (The syntax will be a bit different for the creation of the tables since Ideone is SQLite, though.)

Upvotes: 0

bobs
bobs

Reputation: 22184

If all stores have all departments, then you might try a CROSS JOIN

SELECT store, department
FROM stores
CROSS JOIN departments

Upvotes: 4

Related Questions