Solved

How to outer join without unpurged data?

Posted on 2013-05-11
15
534 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 

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 32

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
 

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 49

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 49

Expert Comment

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

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

615 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