SQL JOIN several tables help required

[mfacer]

Hi guys,

I've so far had no luck getting help or solving this on my own! I have four tables which I need to join together. They are

METRICS
========
metricID	processID	name

---
R3451		1.01		Metric1
R7764		1.01		Metric2
R0443		1.01		metric3
---


METRICS_CUSTOM_REPORT
=====================
metricID	processID	orgID

---
R3451		1.01		mydemo
R7764		1.01		mydemo
---

METRICS_CALCULATED_ANSWERS
==========================
metricID	processID	answer	year

---
R3451		1.01		12.34	2007
R7764		1.01		114.55	2007
---



METRICANSWERS
=============
metricID	orgID	target	year

---
R3451		mydemo	13.00	2007
---

SO - I need ALL the records from the METRICS_CUSTOM_REPORT table based on the processID, then I need to look up the name from METRICS table. I then need ANY records in the METRICS_CALCULATED_ANSWERS and METRICANSWERS table (IF they exists). There will ALWAYS be a record in METRICS which matches the ID number from METRICS_CUSTOM_REPORT table.

Here's what I have so far - but I cannot get in the metricanswers table. What am I doing wrong?

SELECT 
m.*,
c.*,
mca.*
FROM metrics_custom_report c
LEFT JOIN metrics m ON c.metricID = m.metricID
LEFT JOIN metric_calculated_answers mca ON mca.metricID = c.metricID
WHERE c.orgID='$orgID' AND
c.processID='$processID' AND
c.orgID='$orgID' AND
m.processID='$processID' AND
mca.processID='$processID' AND mca.orgID='$orgID' AND mca.year='$year'
GROUP BY m.metricID

Thanks for any help.

 

[CajunCenturion]

You might something like the following:
[tt]
SELECT C.*, M.name
FROM metrics_custom_report C, metrics M
LEFT JOIN metric_calculated_answers A ON A.processID = C.processID
LEFT JOIN metricanswers W ON W.metricid = C.metricid
WHERE C.metricid = M.metricid
GROUP BY C.metricid
ORDER BY C.metricid
[/tt]

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein