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
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?
Thanks for any help.
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
Code:
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?
Code:
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.metricIDThanks for any help.