• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 318
  • Last Modified:

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.
0
splanton
Asked:
splanton
1 Solution
 
QlemoC++ DeveloperCommented:
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

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now