Mulitle Table Joins, GROUP BY, ORDER BY problem

[devm4n]

I wish to return the lastest phase value for a particular company. I am able to return all of these values, but as soon as they are grouped the ORDER BY statement is ignored and only the first phase value is returned.

 

These are the tables/fields  I am querying:

 

COMPANY

company_id

company_name

 

PROJECT

project_id

project_name

 

PROJECT_COMPANY

project_company_id

project_id

company_id

 

PROJECT_PHASE

project_phase_id

project_id

project_phase_order

project_phase_img

 

Each Project can have a of companies associated with it (stored in PROJECT_COMPANY) and a company can be associated with a number of Projects.

Each Project has a number of phases (PROJECT_PHASE) that are sequenced using project_order - the largest being the most recent phase.

 

What I wish to do is return the most recent project phase images (project_phase_img) for a company that is associated with any number of projects.

So if a company is used in 2 projects it will return 2 images from the PROJECT_PHASE that are the most recent.

 

I have been able to return all phase image results, but not just the two required, as soon as they are GROUPED the ORDER BY is ignored so only the initial project_phase_img is returned.

This is the closes SQL I have used:

 

SELECT proj.project_name, projp.project_phase_order, projp.project_phase_img

FROM PROJECT_PHASE AS projp

LEFT JOIN PROJECT_COMPANY AS projc

ON projp.project_id = projc.project_id

LEFT JOIN PROJECT AS proj

ON proj.project_id = projc.project_id

WHERE projc.company_id = '4'

GROUP BY proj.project_id

ORDER BY projp.project_phase_order DESC

 

 

If I dont 'GROUP BY proj.project_id' all phase results are returned and I only need the most recent (highly numbered project_phase_order) project_phase_image.

 

Can this be done in a single SQL statement in MySQL - maybe I need to execute 2 seperate queries, but I would prefer not to.

A solution to this problem would be greatly appreciated!

[gassaz]

Try changing projp.project_phase_order by MAX(projp.project_phase_order)

 

SELECT proj.project_name, MAX(projp.project_phase_order), projp.project_phase_img

FROM PROJECT_PHASE AS projp

LEFT JOIN PROJECT_COMPANY AS projc ON projp.project_id = projc.project_id

LEFT JOIN PROJECT AS proj ON proj.project_id = projc.project_id

WHERE projc.company_id = '4'

GROUP BY proj.project_id

ORDER BY projp.project_phase_order DESC

[fenway]

Not sure what the relationship between project_name and project_phase_img is... but it might be garbage output.

[tjbourke]

I think you want something like this... No doubt this query will error, but use the idea and get it working, the idea worked for me with a similar problem.

SELECT proj.project_name, projp.project_phase_img,

(SELECT MAX(projp.project_phase_order)

FROM PROJECT_PHASE projp2

WHERE projp2.project_id = projp.project_id

)

FROM PROJECT_PHASE AS projp

LEFT JOIN PROJECT_COMPANY AS projc

ON projp.project_id = projc.project_id

LEFT JOIN PROJECT AS proj

ON proj.project_id = projc.project_id

WHERE projc.company_id = '4'

GROUP BY proj.project_id, projp.project_phase_img

 

To get a full explanation of this and why it works, check my blog post on this topic:

http://www.tjbourke.com/6