Confusing inner/outer join issue...

Question

Post reply

Confusing inner/outer join issue...

tcnolan

SSCrazy

Points: 2097
More actions
May 19, 2009 at 10:52 pm

#205021

First of all, I would rather not be just given an answer to this question, just pointed in the right direction, unless of course the answer is that this simply is not possible. I feel like there has to be a way to do this, I'm just having trouble figuring it out. I believe outer joins will have to be used to get the result I want, however I'm not sure exactly...
I've written this into a hypothetical situation to not divulge any sensitive information, but this situation illustrates the problem perfectly.
Hypothetical Situation:
A company library is writing software to track book borrowing to get out of the dark ages of paper borrowing slips. Certain books are restricted and can only be borrowed by certain departments or certain employees. This gives us three basic objects that are relevant to the problem: Books, Departments, and Employees. Employees are all members of at least one Department, and Book borrowing can be granted to entire Departments or specific Employees.
The database contains the following relevant tables:
Employee: Employee information such as name, address, etc.
Department: Departments information such as Primary Location, Department Code, etc.
Book: Book information such as ISBN number, Author, etc.
EmpToDep: Used to associate an employee with a department
EmpToBk: Used to assign borrow permissions to an employee
DepToBk: User to assign borrow permissions to a department
Question:
Would it be possible to run a single select statement that returns every book that an employee is allowed to borrow whether it is explicitly permitted to him or to his department?
I would like to essentially combine the following select statements into a single select statement [edit] I don't want to do something like UNION ALL to accomplish this though.[/edit]:
SELECT Employee.name, Book.isbn
FROM Employee
INNER JOIN EmpToDep ON Employee.emp_id = EmpToDep.emp_id
INNER JOIN Department ON EmpToDep.dep_id = Department.dep_id
INNER JOIN DepToBk ON Department.dep_id = DepToBk.dep_id
INNER JOIN Book ON DepToBk.book_id = Book.book_id
SELECT Employee.name, Book.isbn
FROM Employee
INNER JOIN EmpToBk ON EmpToBk.emp_id = Employee.emp_id
INNER JOIN Book ON EmpToBk.book_id = Book.book_id
Again, I would rather be pointed in the right direction than to be given the exact solution.
[edit]
I wanted to add the following image which illustrates the data that I am looking for here...
[/edit]

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

mayank jarsaniya Hall of Fame Points: 3532 More actions · Answer 1

there should be

book intersect employee

left outer join

book intersect employee intersect department

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

Not having any DDL or sample data available (sorry, but I'm not taking the time to create it), the simpliest way to do it based on what you have provided is a UNION query. I don't see any reason at this point to try and fiqure out a single query that may include both inner and outer joins that can be accomplished much easier with two simple unioned queries.

arjun.tewari Ten Centuries Points: 1013 More actions · Answer 3

Hey Nolan,

It was being very difficult for me to guide you toward the lane however, below is the solution for your situation.

create table book(id int identity(1,1), name varchar(20))

create table employee(id int identity(1,1), name varchar(20))

create table department(id int identity(1,1), name varchar(20))

create table eTOd(did int, eid int)

create table eTOb(bid int, eid int)

create table dTOb(bid int, did int)

insert into book select 'Harry Potter 1'

insert into book select 'Harry Potter 2'

insert into book select 'Harry Potter 3'

insert into book select 'Harry Potter 4'

insert into book select 'Harry Potter 5'

insert into employee select 'Sam'

insert into employee select 'John'

insert into employee select 'Joseph'

insert into department select 'Production'

insert into department select 'RnD'

insert into eTOd select 1, 1

insert into eTOd select 1, 2

insert into eTOd select 2, 3

insert into eTOb select 1, 1

insert into eTOb select 1, 2

insert into eTOb select 1, 3

insert into eTOb select 2, 3

insert into eTOb select 3, 3

insert into dtob select 4, 1

insert into dtob select 5, 1

select * from book

select * from employee

select * from department

select * from dtob

select * from etod

select * from etob

select distinct a.name, f.name from

employee a join etod b on a.id = b.eid

left join etob e on a.id = e.eid

left join dtob d on b.did = d.did

left join book f on (f.id = e.bid or f.id = d.bid)

Here in the output, you can see that "John" has access to "Harry Potter 1" which is from the direct permission to him and to "Harry Potter 4" and to "Harry Potter 5" for which the permission is given to the department. Same is the case with "Sam". But if you see "Joseph" he only has access to "Harry Potter 1, 2 and 3" for which the access is given directly to him.

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 4

First of all, for all your future posts read this article on how to post questions to getter better responses[/url]

Secondly, thanks arjun for preparing sample data and the scripts

Lastly, here is the solution to the problem for the sample data.

set nocount on

if ( object_id( 'tempdb..#book' ) IS NOT NULL )

drop table #book

if ( object_id( 'tempdb..#employee' ) IS NOT NULL )

drop table #employee

if ( object_id( 'tempdb..#department' ) IS NOT NULL )

drop table #department

if ( object_id( 'tempdb..#eTOd' ) IS NOT NULL )

drop table #eTOd

if ( object_id( 'tempdb..#eTOb' ) IS NOT NULL )

drop table #eTOb

if ( object_id( 'tempdb..#dTOb' ) IS NOT NULL )

drop table #dTOb

create table #book(id int identity(1,1) primary key clustered, name varchar(20))

create table #employee(id int identity(1,1) primary key clustered, name varchar(20))

create table #department(id int identity(1,1) primary key clustered, name varchar(20))

create table #eTOd(did int, eid int, primary key clustered( eid, did ) )

create table #eTOb(bid int, eid int, primary key clustered( eid, bid ) )

create table #dTOb(bid int, did int, primary key clustered( did, bid ) )

insert into #book( [name] )

select 'Harry Potter 1'

union all select 'Harry Potter 2'

union all select 'Harry Potter 3'

union all select 'Harry Potter 4'

union all select 'Harry Potter 5'

insert into #employee( [name] )

select 'Sam'

union all select 'John'

union all select 'Joseph'

insert into #department( [name] )

select 'Production'

union all select 'RnD'

insert into #eTOd( did, eid )

select 1, 1

union all select 1, 2

union all select 2, 3

insert into #eTOb( bid, eid )

select 1, 1

union all select 1, 2

union all select 1, 3

union all select 2, 3

union all select 3, 3

insert into #dTOb( bid, did )

select 4, 1

union all select 5, 1

select*

from#employee e

cross join #book b

whereexists( select * from #eTOb e2b where e.id = e2b.eid and b.id = e2b.bid )

or exists( select * from #dTOb d2b inner join #eTOd e2d on d2b.did = e2d.did where e.id = e2d.eid and b.id = d2b.bid )

--Ramesh

tcnolan SSCrazy Points: 2097 More actions · Answer 5

I appreciate the replies. Ramesh, I realized after posting that I should have included sample data, but thought that with the amount of information there, it really would come down to more of a theory kind of response anyway. At any rate, sorry for making you guys do the extra work of that part.

So, both of those do what I was looking for, the most interesting thing is the execution plan for the two solutions...

--Ramesh solution (slightly modified)

select * from employee e

cross join book b

where exists( select * from eTOb e2b where e.id = e2b.eid and b.id = e2b.bid )

or exists( select * from dTOb d2b inner join eTOd e2d on d2b.did = e2d.did where e.id = e2d.eid and b.id = d2b.bid )

--arjun.tewari solution

select distinct a.name, f.name from

employee a join etod b on a.id = b.eid

left join etob e on a.id = e.eid

left join dtob d on b.did = d.did

left join book f on (f.id = e.bid or f.id = d.bid)

The query cost of Ramesh's solution is 38% of the batch whereas arjun's is 62%

arjun's solution was closest to what I was looking for because I wanted to avoid simply combining two separate queries through a union or a cross join, however they both work perfectly well. Regardless of viable solutions, I have already made changes to the real application so that this type of query isn't necessary, but I wanted to post it to try and understand the problem better so I thank both of you for helping me with that.

tcnolan SSCrazy Points: 2097 More actions · Answer 6

Actually, after plugging these two solutions into the actual database that have proper indexes, the optimization goes the other direction. 44% for arjun solution and 56% for ramesh solution. Now I'm very curious to see if there are more optimizations to be made.