SQL Query for data with multiple conditions

user_15464596 0
Viewed 112

I'm trying to find a way to create a query that returns transactions from individuals when they submit transactions that are traditionally accompanied by other transactions.

Example (with data below):

We see employees had a flight purchased (with the date the flight was purchased). Then they submitted baggage fees with transaction dates that match either the departure date and the return date. We then see they submitted a third one that doesn't match any of the airline dates.

Is there a way to identify for each person that has a baggage fee if they have an airline departure/return date that matches the transaction date? In the example below, the last baggage fees for each employee are the rows I'm hoping to return since they don't match the depart/return date for their respective flights.

DB NAME = ExpenseTable

Employee Type TransactionDate Depart Return
123 Airline 12052019 12082019 12132019
123 Baggage 12082019 NULL NULL
123 Baggage 12132019 NULL NULL
123 Baggage 12252019 NULL NULL
567 Airline 11052020 11082020 11132020
567 Baggage 11082020 NULL NULL
567 Baggage 11132020 NULL NULL
567 Baggage 11252020 NULL NULL
fraud-prevention mysql sql
1 Answers

I think you could use "WHERE NOT EXISTS" to do what you're looking for. Something like this:

SELECT *
FROM Transactions T1
WHERE T1.Type = 'Baggage'
AND NOT EXISTS ( 
    SELECT *
    FROM Transactions T2
    WHERE T2.Type = 'Airline'
        AND (T1.TransactionDate = T2.Depart OR T1.TransationDate = T2.Arrive)
    )

The NOT EXISTS excludes any baggage records that have a matching airline record, based on either the depart date or arrive date. (Putting EXISTS by itself would do the opposite...it would only show baggage transactions with matching airline transactions.) You could probably do this with a join, but EXISTS/NOT EXISTS is nice because it doesn't give you duplicate rows in your result set.

user_7968080
Related