Need help with a mysql query

kschuster

Running Mysql,

Three tables. estimate_request, proofs, and _artists

Relationships:

estimate_request -> proofs : 1 to many or none
estimate_request -> artists : one to one or none

My query must select all from _estimate_request where status = active, the associated artist is any, AND the most recent proof record (if any) based on the column action_date.

The following gives me all desired records PLUS a duplicate where there are more then one entry in the proof table for a given estimate_request record.

SELECT *
FROM estimate_request e
LEFT OUTER JOIN proofs p ON e.estimateID = p.estimateID
LEFT OUTER JOIN _artists a ON e.artistID = a.ID
WHERE
e.status='Active'

Hope this makes sense.

Thanks
KEith

barand

Does this improve things?

SELECT DISTINCT *
FROM estimate_request e
LEFT OUTER JOIN proofs p ON e.estimateID = p.estimateID
LEFT OUTER JOIN _artists a ON e.artistID = a.ID
WHERE
e.status='Active'

kschuster

Nope. I tried that. Still gives me all of the desired records plus duplicates.

Keep in mind I only want the latest records from _proofs

barand

Taking date into account

SELECT DISTINCT *
FROM estimate_request e
LEFT OUTER JOIN proofs p ON e.estimateID = p.estimateID
LEFT OUTER JOIN _artists a ON e.artistID = a.ID
WHERE
e.status='Active'
ORDER BY p.action_date DESC LIMIT 1

kschuster

That returns 1 record when there should be 4

barand

You said you wanted the latest.

kschuster

there are 4 records in estimate_request which should be displayed. Each of these records MAY have 0 or many records in proofs. If there is more then one record in proofs I want to show the data from the record in proofs which has the latest action_date.

barand

DISTINCT would be more effective if you specify the actual fields you need rather than 'SELECT *'

kschuster

ok...

I modified the sql to:
SELECT DISTINCT e.estimateid, a.artist_lname,p.action_date,e.status,e.create_date
FROM estimate_request e
LEFT OUTER JOIN proofs p ON e.estimateID = p.estimateID
LEFT OUTER JOIN _artists a ON e.artistID = a.ID
WHERE
e.status='Active'

I get all of the records I should PLUS one duplicate estimateID

strange

puzling

The obvious question: have you acutally checked for duplicate entries in _estimate_request?

Back to the issue:

A cursor could handle this operation easily (if not with great expense). A limitation of rows will affect all rows (as this is a join).

If you must return a single data set, then figure out how to create a temp table with only the latest date per estimateID for the proof table only. Then join your estimate table with the temp table.

(I should warn you that I generally miss the easy route)

kschuster

no dups in that table....