SQL JOIN help required

mfacer

Hi guys,

I have three tables which I am trying to join.

Metrics
metric_calculated_answers
metric_custom_report

The metric_custom_report table is the main one and all the results from that table will be shown (meeting the criteria)... I then need to look up in the metrics table for names of ID numbers stored in the custom_reports table.

I then would like to get get ALL the answers in the metric_calculated_answers table which meet the ID numbers in the custom_report table. the trouble is, the SQL I have used seems to repeat each row 4 times.

Is it possible or should I use two queries??

SELECT 
m.*,
c.*
FROM metrics_custom_report c
LEFT JOIN metrics m 
ON c.metricID = m.metricID
WHERE c.orgID='$orgID' AND
m.processID='$processID' AND
c.processID='$processID'
ORDER BY c.headline ASC, c.is_top_level DESC, c.hierarchy ASC

The above works nicely and gives me the data OK... when I try this though, thats when each row is replicated.

SELECT 
m.*,
c.*,
mca.*
FROM metrics_custom_report c
LEFT JOIN metrics m 
ON c.metricID = m.metricID
RIGHT JOIN metrics_calculated_answers mca
ON mca.metricID = c.metricID
WHERE c.orgID='$orgID' AND
m.processID='$processID' AND
c.processID='$processID' AND
mca.orgID='$orgID' AND mca.processID='$processID'
ORDER BY c.headline ASC, c.is_top_level DESC, c.hierarchy ASC

any help is much appreciated!!

kakki

if there is a c.metricID for each m.metricID,
i think you should remove the "LEFT"

mfacer

I just tried that and it's still getting 4x for each record.... thanks anyway 🙂

mfacer

maybe if I put the table structures too - I need ALL the fields which I have listed.

METRICS_CUSTOM_REPORT

metricID
orgID
processID

METRICS

metricID
name

METRIC_CALCULATED_ANSWERS

metricID
answer

So I do the query on the first metric_custom_reports table and get the metric IDs. I then need to look up each metricID's name (this will ALWAYS exist) then I need to get the answer for each metric (IF it exists).

any ideas?! I am lost with this!!

Piranha

Why do you use a RIGHT JOIN?

Are the returned rows exactly the same, or is there a difference (in id)?

How do your tables look like? Please show them.

Are the tables normalized? From your query it doesn't seem like it.

mfacer

I thought I'd join one left, then one right - to be honest, I was just trying different things to see what might work!!

I only need to join the metrics table in order to get the name of metrics (based on their ID)... then the answers table I need to get any answers which might be there. So there will either be more or equal number of rows in the report table and answers.

not sure what you mean by normalised?

mfacer

If I add

GROUP BY m.metricID at the end it works, ie: only shows one record per metric ID instead of 4 times - is this OK to do?

mfacer

I now need to get information from two other tables! They have metricID in there too.

So, in summary.... I need ALL the records from metric_custom_reports then ANY records in the other tables which match the metricID. I dont think joining them is the right thing to do?? Maybe nested SELECTs ??

Or should I just get the metrics from the custom_report table, then when looping through the results, do more SQL queries for the other data?? That seems stupid though as the page would have to do hundreds of database queries....