TSql for each record in table a find all records in table b that do NOT EXIST in the many to many table C (containing aID & bID)

I have a 2008 Sql Dbase with three tables; Report (ReportID), ReportSection (ReportSectionID) and a many to many table ReportDetail (ReportDetailID, ReportID, ReportSectionID)

What I want to find is, for each Report, what are all the ReportSections that are not in ReportDetail.

Structure.png
Thanks
KimPoveyAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dsackerContract ERP Admin/ConsultantCommented:
Hi, Kim. The following will show the missing possible combinations:

DECLARE @Report TABLE (
    ReportID    varchar(8)  NOT NULL    )

INSERT INTO @Report VALUES ('Report A')
INSERT INTO @Report VALUES ('Report B')

DECLARE @ReportSection TABLE (
    ReportSectionID   varchar(9)  NOT NULL    )

INSERT INTO @ReportSection VALUES ('Section A')
INSERT INTO @ReportSection VALUES ('Section B')
INSERT INTO @ReportSection VALUES ('Section C')

DECLARE @ReportDetail TABLE (
    ReportID        varchar(8)  NOT NULL,
    ReportSectionID varchar(9)  NOT NULL    )

INSERT INTO @ReportDetail VALUES ('Report A', 'Section A')
INSERT INTO @ReportDetail VALUES ('Report A', 'Section C')
INSERT INTO @ReportDetail VALUES ('Report B', 'Section B')

SELECT  *
FROM    @Report, @ReportSection
EXCEPT
SELECT  *
FROM    @ReportDetail

Open in new window

KimPoveyAuthor Commented:
Thanks dsackder,

I was hoping to make this a view.  Is there a way to do this in a single statement without declaring temporary table / variables?
dsackerContract ERP Admin/ConsultantCommented:
If you prefer a query that uses a JOIN instead of two tables separated by commas, the following also works:
SELECT  *
FROM    @Report
JOIN    @ReportSection ON 1 = 1
EXCEPT
SELECT  *
FROM    @ReportDetail

Open in new window

The idea is to force a Cartesian result between the Report and ReportSection tables, then use the EXCEPT to take away those that already exist in the many-to-many table.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

dsackerContract ERP Admin/ConsultantCommented:
Here is what a view would look like:

CREATE VIEW LeftOvers AS
SELECT  ReportId, ReportSectionId
FROM    Report, ReportSection
EXCEPT
SELECT  ReportId, ReportSectionId
FROM    ReportDetail

Open in new window

Obviously, rename "LeftOvers" to whatever you prefer.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
KimPoveyAuthor Commented:
Thanks dsacker exactly what I needed :)
PortletPaulEE Topic AdvisorCommented:
the syntax for a cross join is just that: CROSS JOIN

no need for the comma or for joining via 1=1


CREATE VIEW LeftOvers AS
SELECT  ReportId, ReportSectionId
FROM    Report
CROSS JOIN ReportSection
EXCEPT
SELECT  ReportId, ReportSectionId
FROM    ReportDetail


to me the absolute advantage of spelling out this type of join is that the next person to look at the code will know you really did want that cartesian product
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.