MySQL query nested where condition

Question

In my database, there are four types of individuals, youngOrc, oldOrc, youngHuman, oldHuman.

An individual belongs to a type conditional on a date range. Thus, I have tables like this for each type:

youngOrcList
-------------------
individual_id, 
start_date, 
end_date

This means that the guy with individual_id is a youngOrc between the start and end date. He may be something else outside that range. Similarly, I have a youngHuman table.

Now, the real table of interest that I want to filter is events:

events
------------------
source_individual_id
target_individual_id
event_date

Events table records all events between two individuals in my realm. I want to filter so that I only select events between youngOrc and youngHuman.

So this is a "nested" condition of sort, where I need both events.source_individual_id IN youngOrcList AND events.event_date BETWEEN youngOrcList.start_date AND youngOrcList.end_date. How to make this work?

(Also, any suggestion regarding a better title would be great. I don't even know what to call this and thus unable to Google effectively.)

What did you try so far? Also it will help if you provide sample data and expected results. — Bulat
– Bulat, Commented Sep 17, 2014 at 16:04
It sounds like you have a bad schema. To me, there is no reason to have 4 separatetables for these for classes of individuals. Why not just a single table with all individuals. Old/young is really just a property of the individual (and I'm guessing could change over time - so what are you going to move individuals from one table to another as they age in your current scheme?). Also human vs. orc could be looked at as a property of the individual as well. Why not just have individuals and events which relates to individuals? — Mike Brant
– Mike Brant, Commented Sep 17, 2014 at 16:14
The youngOrcList things are tables I create myself to facilitate the query. The real schema has a table that maps individuals to their types conditional on date range like you said. Would using that table be easier for me? — Heisenberg
– Heisenberg, Commented Sep 17, 2014 at 16:17

Bulat · Accepted Answer · 2014-09-17 16:15:35Z

1

This will give you all events between youngOrc and youngHuman:

SELECT * 
FROM
 Events e
 WHERE EXISTS (
       SELECT * FROM youngHumanList 
       WHERE individual_id IN (e.source_individual_id, e.target_individual_id)
             AND e.event_date BETWEEN start_date AND end_date)
   AND EXISTS (
       SELECT * FROM youngOrcList
       WHERE individual_id IN (e.source_individual_id, e.target_individual_id)
             AND e.event_date BETWEEN start_date AND end_date)

answered Sep 17, 2014 at 16:15

Bulat

6,9992 gold badges32 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Heisenberg Over a year ago

Could you explain how this work? First, I thought IN must be followed by a SELECT subquery. I didn't expect IN (e.source_individual_id, e.target_individual_id) to work (it does). Second, e.event_date BETWEEN start_date AND end_date -- how do I know it's the event_date associated with the individual_id in question?

Bulat Over a year ago

IN operator accept a list of values as well as sub-queries. So you can write WHERE a IN (1, 2, 3). As for event_date it always comes from current record in Events record set. So both exists clauses will have same e.event_date value, but we are looking for existence of Orc and Human separately and only if both exist for an event we return the record

Heisenberg Over a year ago

So, even though events has millions of rows, in each iteration, e.source_individual_id, e.target_individual_id, and e.event_date are all looking at the same row?

Bulat Over a year ago

Yes, and that is controlled by alias e which is pointing to the same record

mirelon · Accepted Answer · 2014-09-17 16:37:52Z

0

You can use join on tables. They can be joined on a field individual_id so then the only condition left for WHERE clause will be the date condition:

SELECT *
FROM youngOrcList AS o
JOIN events AS e ON e.source_individual_id = o.individual_id
JOIN youngHumanList AS h ON h.individual_id = e.target_individual_id
WHERE e.event_date BETWEEN o.start_date AND o.end_date
AND e.event_date BETWEEN h.start_date AND h.end_date

Alternatively, the WHERE condition could be:

WHERE e.event_date > MAX(o.start_date, h.start_date)
AND e.event_date < MIN(o.end_date, h.end_date)

edited Sep 17, 2014 at 16:37

answered Sep 17, 2014 at 16:07

mirelon

5,0166 gold badges42 silver badges72 bronze badges

Collectives™ on Stack Overflow

MySQL query nested where condition

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related