[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


SQL Server 2008 combining a query.

Posted on 2011-10-06
Medium Priority
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

Daniel Wilson earned 2000 total points
ID: 36924602
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


Author Closing Comment

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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

872 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