SQL Server 2008 combining a query.
Posted on 2011-10-06
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 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:
I need to combine this query with another one.
The second query is
SELECT Id,Description FROM Site
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.