SQL Server 2008 combining a query.

Posted on 2011-10-06
Last Modified: 2012-08-13
Frirst: A bit of background...

I have two tables related one to many with a third as a resolving table :

WasteTyperesolve (resolving table)

They look like this:

Id      Description
1      Cardboard
2      Paper
3      Cans
4      General

Id      Description
1      Cardboard
2      Paper

Id      ParentId      ChildId
1      4       1
2      4      2

The following query combines the two tables, ignoring any WasteType records that have WasteSubtypes:

SELECT [Description] FROM [WasteType]
WHERE [WasteTypeId] NOT IN (SELECT [WasteTypeId] FROM [WasteSubTypeResolve])
UNION SELECT [Description] FROM [WasteSubType] ORDER BY [Description]

It would produce:


The question...

I need to combine this query with another one.

The second query is

SELECT Id,Description FROM Site

and returns:
Id      SiteDesc
1      London
2      Brighton
3      Manchester

I need to combine these queries 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

I did post a similar question earlier in the day about combining the results of two tables but that was BEFORE I was informed hat the first query was a little more complicated that I had originally been led to believe.
Question by:splanton
    LVL 32

    Accepted Solution

    how's this?
    SELECT S.ID, S.Description, W.[Description]  FROM [WasteType] W, Site S
    WHERE [WasteTypeId] NOT IN (SELECT [WasteTypeId] FROM [WasteSubTypeResolve])
    UNION SELECT S.ID, S.Description, W.[Description] FROM [WasteSubType]W, Site S ORDER BY W.[Description]

    Open in new window

    LVL 2

    Author Closing Comment

    A very clear,concise more importantly working) solution. Many thanks.

    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    Join & Write a Comment

    In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
    Viewers will learn how the fundamental information of how to create a table.
    Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

    746 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

    16 Experts available now in Live!

    Get 1:1 Help Now