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

Posted on 2011-10-06
Last Modified: 2012-06-21
I have a requirement to display the result of two unrelated select statements.

For example:

Query 1:

SELECT Id,Description FROM Site

Id      SiteDesc
1      London
2      Brighton
3      Manchester

Query 2

SELECT Id,Description FROM WasteStream

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.
Question by:splanton
    LVL 67

    Accepted Solution

    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;
    LVL 75

    Expert Comment

    by:Anthony Perkins
    Something like this (fix the obvious typo in xSELECT):
    xSELECT  s.Id,
    FROM    Site s
            CROSS JOIN WasteStream w

    Open in new window

    LVL 5

    Expert Comment

    Select *
    From Site
    Cross Join WasteStream

    Open in new window


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
    Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now