SQL JOIN several tables help required
SQL JOIN several tables help required
(OP)
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
---
========
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.metricID
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.
Talk To Other Members
RE: SQL JOIN several tables help required
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
Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein