Combine two unrelates select statements into one set of results (cartesian)

I have a requirement to display the result of two unrelated select statements.

For example:

Query 1:

SELECT Id,Description FROM Site

Returns:
Id      SiteDesc
1      London
2      Brighton
3      Manchester


Query 2

SELECT Id,Description FROM WasteStream

Returns:
Id      WasteStreamDesc
1      Cardboard
2      Paper
3      Cans

I need to show the following:

Id      SiteDesc      WasteStreamDesc
1      London      Cardboard
1      London      Paper
1      London      Cans
2      Brighton      Cardboard
2      Brighton      Paper
2      Brighton      Cans
3      Manchester      Cardboard
3      Manchester      Paper
3      Manchester      Cans

This may seem a daft query but it it part of something much bigger and will be used iterativly in a function.

Any help would be appreciated.
LVL 2
splantonAsked:
Who is Participating?
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Just do!
  Select s.*, ws.WasteStreamDesc from Site s, WasteStream ws;
will combine any record of Site with WasteStream.
You can also use the explicit CROSS JOIN syntax:
  Select s.*, ws.WasteStreamDesc
   from Site s cross join WasteStream ws;
0
 
Anthony PerkinsCommented:
Something like this (fix the obvious typo in xSELECT):
xSELECT  s.Id,
        s.SiteDesc,
        w.WasteStreamDesc
FROM    Site s
        CROSS JOIN WasteStream w

Open in new window

0
 
bitrefCommented:
Select *
From Site
Cross Join WasteStream

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.