I've created a query that returns the results I want but I feel there must be a better way to do this. Any guidance would be appreciated.
I am trying to get all items for a specific meeting and join their max meeting date < X and join the max date's committee acronym. X is the current meeting date.
I've tried a few different queries but none, other than the one below, returned the expected results all the time.
You can see this query in action by going to rextester.
DROP TABLE IF EXISTS `committees`; CREATE TABLE committees (`id` int, `acronym` varchar(4)) ; INSERT INTO committees (`id`, `acronym`) VALUES (1, 'Com1'), (2, 'Com2'), (3, 'Com3') ; DROP TABLE IF EXISTS `meetings`; CREATE TABLE meetings (`id` int, `date` datetime, `committee_id` int) ; INSERT INTO meetings (`id`, `date`, `committee_id`) VALUES (1, '2017-01-01 00:00:00', 1), (2, '2017-02-02 00:00:00', 2), (3, '2017-03-03 00:00:00', 2) ; DROP TABLE IF EXISTS `agenda_items`; CREATE TABLE agenda_items (`id` int, `name` varchar(6)) ; INSERT INTO agenda_items (`id`, `name`) VALUES (1, 'Item 1'), (2, 'Item 2'), (3, 'Item 3') ; DROP TABLE IF EXISTS `join_agenda_items_meetings`; CREATE TABLE join_agenda_items_meetings (`id` int, `agenda_item_id` int, `meeting_id` int) ; INSERT INTO join_agenda_items_meetings (`id`, `agenda_item_id`, `meeting_id`) VALUES (1, 1, 1), (2, 1, 2), (3, 2, 1), (4, 3, 2), (5, 2, 1), (6, 1, 3) ; SELECT agenda_items.id, meetings.id, meetings.date, sub_one.max_date, sub_two.acronym FROM agenda_items LEFT JOIN (SELECT ai.id AS ai_id, me.id AS me_id, Max(me.date) AS max_date FROM agenda_items AS ai JOIN join_agenda_items_meetings AS jaim ON jaim.agenda_item_id = ai.id JOIN meetings AS me ON me.id = jaim.meeting_id WHERE me.date < '2017-02-02' GROUP BY ai_id) sub_one ON sub_one.ai_id = agenda_items.id LEFT JOIN (SELECT agenda_items.id AS age_id, meetings.date AS meet_date, committees.acronym AS acronym FROM agenda_items JOIN join_agenda_items_meetings ON join_agenda_items_meetings.agenda_item_id = agenda_items.id JOIN meetings ON meetings.id = join_agenda_items_meetings.meeting_id JOIN committees ON committees.id = meetings.committee_id WHERE meetings.date) sub_two ON sub_two.age_id = agenda_items.id AND sub_one.max_date = sub_two.meet_date JOIN join_agenda_items_meetings ON agenda_items.id = join_agenda_items_meetings.agenda_item_id JOIN meetings ON meetings.id = join_agenda_items_meetings.meeting_id WHERE meetings.id = 2;
REVIEW / TESTING OF ANSWERS (REVISED):*
I've revised the testing based on the comments made.
Since I put a bounty on this question I felt I should show how I'm evaluating the answers and give some feedback. Overall I'm very grateful to all how have helped out, thank you.
For testing, I reviewed the queries against:
- the initial rextester
- a modified version of the initial rextester with all 4 queries for 2 separate datasets
- a larger data set from my actual database
My Original Query with EXPLAIN
+----+-------------+---------------------------+------+----------------------------------------------+ | id | select_type | table | rows | Extra | +----+-------------+---------------------------+------+----------------------------------------------+ | 1 | PRIMARY | meetings | 1 | | | 1 | PRIMARY | join_agenda_item_meetings | 1976 | Using where; Using index | | 1 | PRIMARY | agenda_items | 1 | Using index | | 1 | PRIMARY | <derived2> | 1087 | | | 1 | PRIMARY | <derived3> | 2202 | | | 3 | DERIVED | join_agenda_item_meetings | 1976 | Using index | | 3 | DERIVED | meetings | 1 | Using where | | 3 | DERIVED | committees | 1 | | | 3 | DERIVED | agenda_items | 1 | Using index | | 2 | DERIVED | jaim | 1976 | Using index; Using temporary; Using filesort | | 2 | DERIVED | me | 1 | Using where | | 2 | DERIVED | ai | 1 | Using index | +----+-------------+---------------------------+------+----------------------------------------------+ 12 rows in set (0.02 sec)
Paul Spiegel's answers.
The initial answer works and seems to be the most efficient option presented, much more than mine.
Paul Spiegel's first query pulls the fewest rows, is shorter and more readable than mine. It also doesn't need to reference a date which will be nicer when writing it as well.
+----+--------------------+-------+------+--------------------------+ | id | select_type | table | rows | Extra | +----+--------------------+-------+------+--------------------------+ | 1 | PRIMARY | m1 | 1 | | | 1 | PRIMARY | am1 | 1976 | Using where; Using index | | 1 | PRIMARY | am2 | 1 | Using index | | 1 | PRIMARY | m2 | 1 | | | 2 | DEPENDENT SUBQUERY | am3 | 1 | Using index | | 2 | DEPENDENT SUBQUERY | m3 | 1 | Using where | | 2 | DEPENDENT SUBQUERY | c3 | 1 | Using where | +----+--------------------+-------+------+--------------------------+ 7 rows in set (0.00 sec)
This query also returns the correct results when adding DISTINCT
to the select statement. This query does not perform as well as the first though (but it is close).
+----+-------------+------------++------+-------------------------+ | id | select_type | table | rows | Extra | +----+-------------+------------++------+-------------------------+ | 1 | PRIMARY | <derived2> | 5 | Using temporary | | 1 | PRIMARY | am | 1 | Using index | | 1 | PRIMARY | m | 1 | | | 1 | PRIMARY | c | 1 | Using where | | 2 | DERIVED | m1 | 1 | | | 2 | DERIVED | am1 | 1787 | Using where; Using index | | 2 | DERIVED | am2 | 1 | Using index | | 2 | DERIVED | m2 | 1 | | +----+-------------+------------+------+--------------------------+ 8 rows in set (0.00 sec)
Stefano Zanini's answer
This query does return the expected results using DISTINCT
. When using EXPLAIN
and the number of rows being pulled this query is more efficient when compared to my original one but Paul Spiegel's is just a bit better.
+----+-------------+------------+------+---------------------------------+ | id | select_type | table | rows | Extra | +----+-------------+------------+------+---------------------------------+ | 1 | PRIMARY | me | 1 | Using temporary; Using filesort | | 1 | PRIMARY | rel | 1787 | Using where; Using index | | 1 | PRIMARY | <derived2> | 1087 | | | 1 | PRIMARY | rel2 | 1 | Using index | | 1 | PRIMARY | me2 | 1 | Using where | | 1 | PRIMARY | co | 1 | | | 2 | DERIVED | t1 | 1787 | Using index | | 2 | DERIVED | t2 | 1 | Using where | +----+-------------+------------+------+---------------------------------+ 8 rows in set (0.00 sec)
EoinS' answer
As noted in the comments, this answer works if meetings are sequential, but they may not be unfortunately.
3 Answers
Answers 1
This one is a bit crazy.. Let's do it step by step:
The first step is a basic join
set @meeting_id = 2; select am1.meeting_id, am1.agenda_item_id, m1.date as meeting_date from meetings m1 join join_agenda_items_meetings am1 on am1.meeting_id = m1.id where m1.id = @meeting_id;
We select the meeting (id = 2) and the corresponding agenda_item_ids. This will already return the rows we need with the first three columns.
Next step is to get the last meeting date for every agenda item. We need to join the first query with the join table and corresponding meetings (except of the one with id = 2 - am2.meeting_id <> am1.meeting_id
). We only want meetings with a date before the actual meeting (m2.date < m1.date
). From all those meetings we only want the latest date each agenda item. So we group by the agenda item and select max(m2.date)
:
select am1.meeting_id, am1.agenda_item_id, m1.date as meeting_date, max(m2.date) as max_date from meetings m1 join join_agenda_items_meetings am1 on am1.meeting_id = m1.id left join join_agenda_items_meetings am2 on am2.agenda_item_id = am1.agenda_item_id and am2.meeting_id <> am1.meeting_id left join meetings m2 on m2.id = am2.meeting_id and m2.date < m1.date where m1.id = @meeting_id group by m1.id, am1.agenda_item_id;
This way we get the fourth column (max_date
).
Last step is to select the acronym
of the meeting with the last date (max_date
). And this is the crazy part - We can use a correlated subquery in the SELECT clause. And we can use max(m2.date)
for the correlation:
select c3.acronym from meetings m3 join join_agenda_items_meetings am3 on am3.meeting_id = m3.id join committees c3 on c3.id = m3.committee_id where am3.agenda_item_id = am2.agenda_item_id and m3.date = max(m2.date)
The final query would be:
select am1.meeting_id, am1.agenda_item_id, m1.date as meeting_date, max(m2.date) as max_date, ( select c3.acronym from meetings m3 join join_agenda_items_meetings am3 on am3.meeting_id = m3.id join committees c3 on c3.id = m3.committee_id where am3.agenda_item_id = am2.agenda_item_id and m3.date = max(m2.date) ) as acronym from meetings m1 join join_agenda_items_meetings am1 on am1.meeting_id = m1.id left join join_agenda_items_meetings am2 on am2.agenda_item_id = am1.agenda_item_id and am2.meeting_id <> am1.meeting_id left join meetings m2 on m2.id = am2.meeting_id and m2.date < m1.date where m1.id = @meeting_id group by m1.id, am1.agenda_item_id;
To be true, i was surprised that you can use max(m2.date)
in the subquery.
Another solution - Use the second query in a subquery (derived table). Join committees over meetings and the join table using max_date
. Only keep rows with an acronym and rows without a max_date
.
select t.*, c.acronym from ( select am1.meeting_id, am1.agenda_item_id, m1.date as meeting_date, max(m2.date) as max_date from meetings m1 join join_agenda_items_meetings am1 on am1.meeting_id = m1.id left join join_agenda_items_meetings am2 on am2.agenda_item_id = am1.agenda_item_id and am2.meeting_id <> am1.meeting_id left join meetings m2 on m2.id = am2.meeting_id and m2.date < m1.date where m1.id = @meeting_id group by m1.id, am1.agenda_item_id ) t left join join_agenda_items_meetings am on am.agenda_item_id = t.agenda_item_id and t.max_date is not null left join meetings m on m.id = am.meeting_id and m.date = t.max_date left join committees c on c.id = m.committee_id where t.max_date is null or c.acronym is not null;
Answers 2
Using your schema I used the below query, assuming that all meetings
entries are sequential:
set @mymeeting = 2; select j.agenda_item_id, m.id, m.date, mp.date, c.acronym from meetings m left join join_agenda_items_meetings j on j.meeting_id = m.id left join join_agenda_items_meetings jp on jp.meeting_id = m.id -1 and jp.agenda_item_id = j.agenda_item_id left join meetings mp on mp.id = jp.meeting_id left join committees c on mp.committee_id = c.id where m.id = @mymeeting;
I create a variable just to make it easy to change meetings on the fly.
Here is a functional example in Rextester
Thanks for making your schema so easy to reproduce!
Answers 3
I found this problem quite challenging, and the results I achieved are not jaw-dropping, but I managed to get rid of one of the sub-queries and maybe of a few joins, and this is result:
select distinct me.ID, me.DATE, rel.AGENDA_ITEM_ID, sub.MAX_DATE, co.ACRONYM from MEETINGS me join JOIN_AGENDA_ITEMS_MEETINGS rel /* Note 1*/ on me.ID = rel.MEETING_ID left join ( select t1.AGENDA_ITEM_ID, max(t2.DATE) MAX_DATE from JOIN_AGENDA_ITEMS_MEETINGS t1 join MEETINGS t2 on t2.ID = t1.MEETING_ID where t2.DATE < '2017-02-02' group by t1.AGENDA_ITEM_ID ) sub on rel.AGENDA_ITEM_ID = sub.AGENDA_ITEM_ID /* Note 2 */ left join JOIN_AGENDA_ITEMS_MEETINGS rel2 on rel2.AGENDA_ITEM_ID = rel.AGENDA_ITEM_ID /* Note 3 */ left join MEETINGS me2 on rel2.MEETING_ID = me2.ID and sub.MAX_DATE = me2.DATE /* Note 4 */ left join COMMITTEES co on co.ID = me2.COMMITTEE_ID where me.ID = 2 and (sub.MAX_DATE is null or me2.DATE is not null) /* Note 5 */ order by rel.AGENDA_ITEM_ID, rel2.MEETING_ID;
Notes
you don't need the join with
AGENDA_ITEMS
, since theID
is already available in the relationship tableup to this point we have current meeting, its agenda items and their "calculated" max date
we get all meetings of each agenda item...
...so that we can pick the meeting whom date matches the max date we calculated previously
this condition is needed because all the joins from
rel2
on have to be left (because some agenda item may have no previous meeting and henceMAX_DATE = null
) but this wayme2
would give some agenda items undesired meetings.
0 comments:
Post a Comment