REALLY HARD SQL problem

steverin0

relational schema:

contact {contact_id, First_Name, Last_Name}
Experience {contact_id, credit, startdate, enddate}

This is a movie credit database. Say the report is for Producers, the application needs to return a report with the following columns:

Name - Months as Producer - Most recent credit

Billy Joe - 120 - Executive Producer
Ann Yip - 60 - Producer
Jelly Morton - 24 - Director

The problem is this report must be generated by only one query. It could be generated by two queries, but the following is not fast enough:

SELECT First_Name, Last_Name SUM(PERIOD_DIFF(DATE_FORMAT(enddate,"%y%m")+1, DATE_FORMAT(startdate,"%y%m"))) as months FROM contact, Experience WHERE contact.contact_id=Experience.contact_id and credit="Producer" GROUP BY contact.contact_id ORDER BY months desc;

Followed by a query on each result which said

SELECT credit from Experience WHERE contact_id=$row[contact_id] ORDER BY startdate DESC;

However, because of the number of records which must be returned at once, this method is too slow. The result set must be returned by one query.

Does anyone know the solution to this problem?

tomhath

I'd do it with a temporary table. There are three queries here, but all should be fast and you only need the results from the last one. This works on MySQL version > 3.23. The temporary table will be dropped automatically when you close the connection to the database:

-- temp table to hold most recent credit

create temporary table temp_tab(contact_id int, credit varchar(20));


-- find the credit that does not have another row with a > enddate
-- have to use LEFT JOIN and null trick because subselect not available in MySQL

insert into temp_tab (contact_id, credit)
select t1.contact_id, t1.credit
from Experience t1
left join Experience t2
on t1.contact_id = t2.contact_id
 and t2.enddate > t1.enddate
where t2.contact_id is null;

-- now sum the experience in desired credit and join with the temp tab

SELECT c.First_Name, c.Last_Name, 
SUM(PERIOD_DIFF(DATE_FORMAT(e1.enddate,"%y%m")+1, DATE_FORMAT(e1.startdate,"%y%m"))) as months, 
t1.credit
FROM contact c
INNER JOIN Experience e1
ON c.contact_id=e1.contact_id
LEFT JOIN temp_tab t1
ON c.contact_id=t1.contact_id
WHERE e1.credit="Producer" 
GROUP BY c.contact_id ORDER BY months desc;

Sxooter

You don't mention the database you're using. Are you allowed sub selects and unions or are you running an old flavor of MySQL?

steverin0

Sorry--totally vanilla:

MySQL 4.0.16-standard

/Steve

PS, Sub-select and UNION statements are beyond by present skill level currently.

This is really really hard/