The inverse of a relationship table 1

[icrf]

I have a UsersTable with a UserID and a ReportsTable with a ReportID. I also have an AccessTable with UserID and ReportID for the reports each user has access to.

It's quite simple to get a list of reports users have access to, but how do I get a list of reports users don't have access to? I'm essentially looking for the inverse of the AccessTable, with UserID and ReportID populated. It's easy enough to query for one user, but can I get a result set for all users with one query? This feels like it should be simple, but I'm just not thinking on a Monday.

Thanks.

- Andrew
Text::Highlight - A language-neutral syntax highlighting module in Perl
also on SourceForge including demo

 

[gmmastros]

Try this...

Select A.UserId, A.ReportId
From   (Select UsersTable.UserId,
               Reportstable.ReportId
        From   UsersTable
               Cross Join ReportsTable
       ) As AllCombinations
       Left Join AccessTable
         On AllCombinations.UserId = AccessTable.UserId
         And AllCombinations.ReportId = AccessTable.UserId
Where  AccessTable.UserId Is NULL

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[icrf]

Thank you. Cross join is what my brain was missing.

- Andrew
Text::Highlight - A language-neutral syntax highlighting module in Perl
also on SourceForge including demo

 

[gmmastros]

I forgot to add...

If this works and you would like a full explanation, don't hesitate to ask.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[icrf]

Thanks, but that's the exact method I was thinking of doing, but found myself completely unable to formulate the SQL syntax for doing it. I need more sleep.

Thank you for the quick response. It works great.

[COLOR=#006600]-- Users[/color]
[COLOR=#0000FF]SELECT[/color] * [COLOR=#0000FF]FROM[/color] tblUsers
[COLOR=#006600]-- Reports Available[/color]
[COLOR=#0000FF]SELECT[/color] * [COLOR=#0000FF]FROM[/color] tblReports R [COLOR=#FF0000]JOIN[/color] tblUserReportAccess A [COLOR=#0000FF]ON[/color] R.ReportID = A.ReportID
[COLOR=#006600]-- Reports Unavailable[/color]
[COLOR=#0000FF]SELECT[/color] R.*, C.* [COLOR=#0000FF]FROM[/color] 
	([COLOR=#0000FF]SELECT[/color] UserID, ReportID [COLOR=#0000FF]FROM[/color] tblUsers CROSS [COLOR=#FF0000]JOIN[/color] tblReports) C
	[COLOR=#00B0B0]LEFT[/color] [COLOR=#FF0000]JOIN[/color] tblUserReportAccess A [COLOR=#0000FF]ON[/color] C.UserID = A.UserID [COLOR=#FF0000]AND[/color] C.ReportID = A.ReportID
	[COLOR=#FF0000]JOIN[/color] tblReports R [COLOR=#0000FF]ON[/color] R.ReportID = C.ReportID
[COLOR=#0000FF]WHERE[/color] A.UserID [COLOR=#0000FF]is[/color] [COLOR=#FF0000]null[/color]

- Andrew
Text::Highlight - A language-neutral syntax highlighting module in Perl
also on SourceForge including demo

 

[ESquared]

For what it's worth you don't have to use a derived table. Avoiding it may perform worse, or better. Since you joined back to tblReports again, it probably will perform better now that the extra join is out:

SELECT R.*, U.*
FROM 
    tblUsers U
    CROSS JOIN tblReports R
    LEFT JOIN tblUserReportAccess A ON U.UserID = A.UserID AND R.ReportID = A.ReportID
WHERE A.UserID is null

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.[/color]

 

[icrf]

You're right, execution plan says dropping the derived table has 20% lower cost. They're puny tables and will always be, so in practice, it shouldn't matter, but always good to know. Thanks.

I've never really understood execution plans, past the whole % relative cost numbers, which sometimes don't make much sense. Does anyone have a favorite book or website that goes over the details?

- Andrew
Text::Highlight - A language-neutral syntax highlighting module in Perl
also on SourceForge including demo