Quite complicated mysql query comparing relation matches

I have a mySQL problem, where I need to calculate the SUM of rows after there is a mismatch in two relation tables. There are tests with numeric values and two part lists. Parts are usually included in both lists, and in this case all rows are summed up. Sometimes a part is only in the first list and not in the second, and in these cases all previous test values should be disregarded. In other words, rows should only be included in the sum if their test id is bigger than the last test id where this particular part was in list 1 but not in list 2.

There's a simplified mysql table listing at [url removed, login to view] where you can get an idea of the situation.

In this example, part 10 should sum all three test values, because the part is included in all lists, but part 20 should only return the value for test 3, as in test 2 it was not included in both in and out lists, and test 1 happened before test 2. So part 10 => 111 and part 20 => 100.

The job is to provide me with a MYSQL SELECT command, which returns the results specified above. Usage of complementary PHP and/or multiple queries is allowed, if the resulting code is faster.

I'll provide a more elaborate mysql dump to work with, so you can easily test your work.

Kemahiran: MySQL, PHP

Lihat lagi: matches list, list matches, job matches, quite complicated, relation, query table, mysql test, mysql query, php mysql query multiple, table queries, mysql tables rows, raw php code, mysql query php, select list php mysql, mysql query example, php mysql table, matches, mysql problem, calculate value, php mysql select table, pastebin, pastebin php, php pastebin, php mysql calculate, table dump php

Tentang Majikan:
( 44 ulasan ) Tirana, Finland

ID Projek: #1533869

12 pekerja bebas membida secara purata $71 untuk pekerjaan ini


Mysql expert. please check PM

$150 USD dalam sehari
(56 Ulasan)

Hi,Please check PM

$80 USD dalam 4 hari
(53 Ulasan)

We are a professionally managed IS0 9001:2008 certified web development company. Our team has extensive experience and we have provided services to reputed companies in US, UK, CANADA & Europe. Currently we have around Lagi

$100 USD dalam 2 hari
(5 Ulasan)

Hi, Please see my PM. I'm quite clear about the project but want to discuss more concerning expected runtime and other issues. Thanks!

$50 USD dalam sehari
(55 Ulasan)

Hello, please provide clearer info about your query.

$30 USD dalam sehari
(21 Ulasan)

Hi Please see details of my bid in your PMB

$45 USD dalam 0 hari
(24 Ulasan)

Hi, johka0129. I can do it. Please check PM. Thanks and regards.

$80 USD dalam 0 hari
(23 Ulasan)

Hi, please read the PM.

$30 USD dalam sehari
(16 Ulasan)

Hi, Quite interesting job, I am expert in writing optimized SQL. I believe i can do this job easily Regards, Tariq

$99 USD dalam sehari
(1 Ulasan)

please check pmb.

$90 USD dalam sehari
(0 Ulasan)

Hi, I have gone through your requirement and understood the task we have READY to start right now. \

$30 USD dalam sehari
(0 Ulasan)

We are happy to introduce us as a web-design & development company working in PHP, MySql, Symfony Framework, jQuery, Web-Design & AJAX since 2005. Our expert team always help client to meet any requirement and provide Lagi

$70 USD dalam 5 hari
(0 Ulasan)