Solved

How to outer join without unpurged data?

Posted on 2013-05-11
15
483 Views
Last Modified: 2013-05-11
How can I perform a left outer join on two tables while excluding all items that have a deleted flag set?

All of the tables in my database have a nullable DateRemoved DATETIME field that indicates the user has requested to delete the record from the database.  My application knows to exclude these rows while displaying data.  BOTH TableA and TableB have this field and ONE OR BOTH of these fields may be populated (which means the records must be excluded from the query).

Here's an example...

TABLE: Car
id int PK
car_name varchar(50)
date_deleted datetime null

TABLE: Garage
id int PK
garage_name varchar(50)
date_deleted datetime null

TABLE: OccupiedGarage
id int PK
car_id int
garage_id int
date_deleted datetime null

Open in new window


In this example, any Car or Garage may be created or FLAGGED for deletion.  A single Car may be contained in a single Garage (the OccupiedGarage table).  When the car leaves the garage the OccupiedGarage.date_deleted field is set to a valid value.

In this scenario I need to find all Cars that are NOT deleted and are NOT contained in an OccupiedGarage.
0
Comment
Question by:MrNMrsGeek
  • 6
  • 5
  • 3
  • +1
15 Comments
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 39158132
<< I need to find all Cars that are NOT deleted and are NOT contained in an OccupiedGarage.>>

Is this what you are looking for ?

select C1.car_name
from cars C1
inner join
(
select car_id 
from cars
where date_deleted is null
except
select car_id
from OccupiedGarage)  C2 on C1.car_id=C2.car_id

Open in new window

0
 

Author Comment

by:MrNMrsGeek
ID: 39158158
Hmm... almost... I think.  I do not see how the select on OccupiedGarage is accounting for that table's delete flag.  This is the part that is confusing to me.  The three scenarios that must be accounted for are:

1. Car may be flagged as deleted; OR,
2. OccupiedGarage may be flagged as deleted; OR,
3. BOTH Car and OccupiedGarage may be flagged as deleted.
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 39158183
I am having a lot of difficulties understanding what you are trying to find.  You have asked :

I need to find all Cars that are NOT deleted and are NOT contained in an OccupiedGarage.

and now you are changing the initial question by adding conditions that are not answering the same query.  Can you express what you are looking for in one clear sentence on the same model you did initially.  It will facilitate helping you...

Thanks
0
 

Author Comment

by:MrNMrsGeek
ID: 39158204
I have not changed the initial question.  As I stated:

BOTH TableA and TableB have this field and ONE OR BOTH of these fields may be populated (which means the records must be excluded from the query).

How can I find all Car objects that are NOT flagged for deletion AND are also NOT in a OccupiedGarage record which IS flagged for deletion?

Remember, having EITHER of the DATE_DELETED fields set to a date IN EITHER TABLE means that this record is effectively deleted.  The end result must function in the same way as it would had the record in OccupiedGarage been purged from the table completely.
0
 
LVL 31

Expert Comment

by:awking00
ID: 39158256
I'm still a little unclear as to what you need. Can you provide some sample data for the three tables with all of the various criteria and what you expect as the output?
0
 

Author Comment

by:MrNMrsGeek
ID: 39158357
I'll try...


CONTENTS TABLE Car
10 Roadster (DELETED = 20121231)
11 Coupe (DELETED = NULL)
12 Sedan (DELETED = NULL)

CONTENTS TABLE Garage
20 Left (DELETED = NULL)
21 Middle (DELETED = 20121231)
22 Right (DELETED = NULL)

CONTENTS TABLE OccupiedGarage
30 10 20  (DELETED 20121231)
31 10 21  (DELETED 20121231)
32 10 22  (DELETED 20121231)
33 11 22  (DELETED 20121231)
34 11 21  (DELETED 20121231)
35 11 20  (DELETED = NULL)
36 12 22  (DELETED = NULL)
37 12 20  (DELETED 20121231)
38 12 21  (DELETED 20121231)

FINAL OUTPUT
No results

In this example, what we are looking for is a list of all cars that are NOT safely stored in a garage.  Over time we have had a total of three Cars and three Garages.  We can tell from looking at OccupiedGarage that all three cars were in all three garages at some point in time.  We can see that we returned NO RESULTS because both cars are safely tucked away.

Whenever a car leaves the garage the associated OccupiedGarage the record is flagged as deleted:


CONTENTS TABLE Car
10 Roadster (DELETED = 20121231)
11 Coupe (DELETED = NULL)
12 Sedan (DELETED = NULL)

CONTENTS TABLE Garage
20 Left (DELETED = NULL)
21 Middle (DELETED = 20121231)
22 Right (DELETED = NULL)

CONTENTS TABLE OccupiedGarage
30 10 20  (DELETED 20121231)
31 10 21  (DELETED 20121231)
32 10 22  (DELETED 20121231)
33 11 22  (DELETED 20121231)
34 11 21  (DELETED 20121231)
35 11 20  (DELETED = NULL)
36 12 22  (DELETED 20121231)
37 12 20  (DELETED 20121231)
38 12 21  (DELETED 20121231)

FINAL OUTPUT
12 Sedan

In this example the Sedan was removed from the garage and thus the OccupiedGarage record was marked as deleted.  This causes our report output to change and show one car (the Sedan) as NOT being safely in the garage.

However, if we have actually sold the Sedan, and therefore flag it for deletion, the results would change again to remove it from the list:


CONTENTS TABLE Car
10 Roadster (DELETED = 20121231)
11 Coupe (DELETED = NULL)
12 Sedan (DELETED = 20121231)

CONTENTS TABLE Garage
20 Left (DELETED = NULL)
21 Middle (DELETED = 20121231)
22 Right (DELETED = NULL)

CONTENTS TABLE OccupiedGarage
30 10 20  (DELETED 20121231)
31 10 21  (DELETED 20121231)
32 10 22  (DELETED 20121231)
33 11 22  (DELETED 20121231)
34 11 21  (DELETED 20121231)
35 11 20  (DELETED = NULL)
36 12 22  (DELETED 20121231)
37 12 20  (DELETED 20121231)
38 12 21  (DELETED 20121231)

FINAL OUTPUT
No results

In summary...

A. If a Car exists without an associated OccupiedGarage record then the car is shown in the results.
B. If an OccupiedGarage record is created for this Car then the Car is removed from the reuslts.
C. If a value is added to the DATE_DELETED fiied for the OccupiedGarage record then the Car is added back to the results.
D. If both records exist (Car and OccupiedGarage) and a value is added to the DATE_DELETED fiied for the Car record then the Car is removed from the results.

Does this help?
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 39158365
<<How can I find all Car objects that are NOT flagged for deletion AND are also NOT in a OccupiedGarage record which IS flagged for deletion?>>
The above query is equivalent to...

How can I find all Car objects that are NOT flagged for deletion AND are also in OccupiedGarage record which IS NOT flagged for deletion?

The below query should give you an answer to this last formulation...

select C1.car_name
from cars C1
inner join
(
select car_id 
from cars
where date_deleted is null
intersect
select car_id
from OccupiedGarage 
where date_deleted is not null
)  C2 on C1.car_id=C2.car_id

Open in new window

Hope this is what you are seeking...
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:MrNMrsGeek
ID: 39158585
Nothing is returned.  I started with just three records in the Car table.  No records are in the OccupiedGarage table.  In that scenario, since NO cars are in the Garage then ALL Cars should be returned.  I have attached the DDL for the tables if this is easier.
Sample-DDL.sql
0
 

Author Comment

by:MrNMrsGeek
ID: 39158592
If it helps, this is what I have been using:

SELECT        CarID
FROM            Car
WHERE        (DateDeleted IS NULL) AND (CarID NOT IN
                             (SELECT        CarID
                               FROM            OccupiedGarage
                               WHERE        (DateDeleted IS NULL)))

Open in new window


My concern is the abuse on the tables when the tables have millions of records and 10-20k of active / not-deleted records.
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 39158694
In this case, please try...

select C1.carname
from cars C1
inner join
(
select carid 
from cars
where date_deleted is null
except
select carid
from OccupiedGarage 
where date_deleted is not null
)  C2 on C1.carid=C2.carid

Open in new window

0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 39158695
Sorry I mean...

select C1.carname
from cars C1
inner join
(
select carid 
from cars
where datedeleted is null
except
select carid
from OccupiedGarage 
where datedeleted is not null
)  C2 on C1.carid=C2.carid

Open in new window

0
 

Author Comment

by:MrNMrsGeek
ID: 39158772
Nope.  Doesn't work.  At the moment I have two cars (Roaster & Sedan) only one deleted record in OccupiedGarage.  BOTH cars should be displayed however only Roadster is displayed.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39158916
This is how I have interpreted your question:

include in results all Cars that are:
NOT flagged as deleted in car table (car.date_deleted is null)
if  NOT contained in an OccupiedGarage at all (OccupiedGarage.car_id is null)
but, if contained in OccupiedGarage, also NOT flagged as deleted (OccupiedGarage.date_deleted is null)

I created some sample data, cars 5,10,20
none are 'deleted' in the car table
car_id 20 is 'deleted' in the OccupidedGarage table

select
  car.id
from car
LEFT join OccupiedGarage as OC on car.id = oc.car_id
where car.Date_Deleted IS NULL
and (oc.Date_Deleted IS NULL or oc.car_id is null);

RESULTS: car.id = 5, 10

However, this does not match you existing query

/* as provided, using AND ... NOT IN */
SELECT Car.ID
FROM Car
WHERE (Date_Deleted IS NULL)
AND (
        Car.ID NOT IN (
            SELECT Car_ID
            FROM OccupiedGarage
            WHERE (Date_Deleted IS NULL)
            )
        );
RESULTS: car.id = 5,20 (but 20 is 'deleted')        
   
Please refer to this sqlfiddle: http://sqlfiddle.com/#!3/1fbb1/1

this is the sample tables/data used here

CREATE TABLE Car
	([id] int, [car_name] varchar(8), [date_deleted] datetime)
;
	
INSERT INTO Car
	([id], [car_name], [date_deleted])
VALUES
	(5, 'wagon', NULL),
	(10, 'roadster', NULL),
	(20, 'sedan', NULL)
;

CREATE TABLE OccupiedGarage
	([id] int, [car_id] int, [garage_id] int, [date_deleted] datetime)
;
	
INSERT INTO OccupiedGarage
	([id], [car_id], [garage_id], [date_deleted])
VALUES
	(1, 10, 100, NULL),
	(2, 20, 100, '2013-02-01')
;

CREATE TABLE Garage
	([id] int, [garage_name] varchar(10), [date_deleted] datetime)
;
	
INSERT INTO Garage
	([id], [garage_name], [date_deleted])
VALUES
	(100, 'the garage', NULL)
;

Open in new window

a faster way to resolving this question is to provide sample data and expected results, if my sample data is inadequate could you please provide some that contains the data conditions needed, then identify the expected results from that data.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39158948
forgive me, I see you have provided sample data and expected results: my bad.
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 39159021
I have placed your sample data from ID: 39158357 into a sqlfiddle
http://sqlfiddle.com/#!3/f76a9/3

There you may compare your existing "AND ... NOT IN" query to one using a left join (below)

The following query matches your expected results:
SELECT
      car.id
    , car.car_name
    , car.date_deleted
    , oc.date_deleted AS oc_date_deleted
FROM car
LEFT JOIN OccupiedGarage AS OC
       ON car.id = oc.car_id
      AND oc.Date_Deleted IS NULL
WHERE car.Date_Deleted IS NULL 
AND oc.car_id IS NULL

Open in new window

{+ edit, sorry, correction needed}
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

Suggested Solutions

Creating and Managing Databases with phpMyAdmin in cPanel.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

760 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now