mysql combined table joins

Question

Thank you for your comments I'll try this from a different angle.

How can this query be written to produce these results?

    CompID  CompeteID   Casting     Fishing     Total    
         1      1       265.89    425.56       691.45   
         1      9       212.31     84.76       285.92   
         1      7         0.00    285.92       285.92   
         1      8         0.00     44.52        44.52   
   ORDER BY Total DESC

SELECT cs.CompID, ic.CompeteID 
 , MAX(IF(cast.CastType_ID BETWEEN 6 AND 12, cast.Length, 0)) + SUM(IF(cast.CastType_ID < 6, 40 - (cast.Length * 2), 0)) AS 'Casting' 
FROM `comp-setup` cs
INNER JOIN `input-competitor` ic 
    ON cs.CompID = ic.Comp_ID 
LEFT JOIN `input-casting` cast 
    ON cast.Compete_ID = ic.CompeteID 
GROUP BY ic.CompeteID

UNION ALL 

SELECT cs.CompID, ic.CompeteID
, SUM((iw.Weight * ca.Factor) + '1') AS 'Fishing'
FROM `comp-setup` cs
INNER JOIN `input-competitor` ic 
    ON cs.CompID = ic.Comp_ID 
LEFT JOIN `input-weighin` iw 
    ON iw.Compete_ID = ic.CompeteID
INNER JOIN `input-catchpoints` ca 
    ON ca.PointsCatchID = iw.PointsCatch_ID
GROUP BY ic.CompeteID

Results from the above query, I have added break-line and Fishing header to separate data.

    CompID  CompeteID   Casting 
        1       1        265.89 
        1       7             0 
        1       8             0 
        1       9        212.31 
----------------------------------
                        Fishing
        1       1        425.56 
        1       7        285.92 
        1       8         44.52 
        1       9         84.76

You are using `GROUP BY` incorrectly, because you are selecting non aggregate columns while aggregating only by `CompeteID`. Some sample data would be nice, and also if you can reduce your question to a _minimal_ example that would also help. — Tim Biegeleisen, Aug 03 '17 at 02:11
@Tim Biegeleisen. Not sure what data to provide for which tables or a final results data? the link in comments. Not sure how to reduce the question or if I'm asking the right question. I'm just a fisherman helping my association ;-) — Marko Lucas, Aug 03 '17 at 04:20
There are so many joins that it is hard to follow what is happening. — Tim Biegeleisen, Aug 03 '17 at 04:21
@RoshanaPitigala Please don't include images of text/tables, ask the OP to use text. Even ER diagrams should be included only to agument the DDL that is almost all their content. Images cannot be searched for or cut & pasted. Also typically the OP should be giving an executable [mcve] anyway. — philipxy, Aug 03 '17 at 13:57
A quick impression: If (after you fix the GROUP BY) you left join 1:many on key + column1 because you are aggregating then if you do it again for column2 you get all posible combinations of key-column1-column2 when you only want combos of key-column2. Either do the left joins separately & inner join the result, or drop the extra rows before you left join again, or group inside select. [Strange duplicate behavior from GROUP_CONCAT of two LEFT JOINs of GROUP_BYs](https://stackoverflow.com/a/45252750/3404097). Please read & act on [mcve]. — philipxy, Aug 03 '17 at 14:04

Marko Lucas · Answer 1 · 2017-08-04T05:21:10.377

Days this has taken me to work out. I found this example today which lead me to this solution, please post if there is a better way? But this does what I need!

    SELECT Sub1.CompID, Sub1.CompeteID, Sub2.Casting, Sub1.Fishing, Sub2.Casting + Sub1.Fishing AS Total
FROM 
    (SELECT cs.CompID, ic.CompeteID, SUM((iw.Weight * ca.Factor) + '1') AS Fishing
FROM `comp-setup` cs
    INNER JOIN `input-competitor` ic ON cs.CompID = ic.Comp_ID 
    INNER JOIN `input-weighin` iw ON iw.Compete_ID = ic.CompeteID
    INNER JOIN `input-catchpoints` ca ON ca.PointsCatchID = iw.PointsCatch_ID
    INNER JOIN `list-grade` gr ON ic.Grade_ID = gr.GradeID 
    INNER JOIN `list-division` ld ON ic.Div_ID = ld.DivID 
    GROUP BY ic.CompeteID) Sub1

INNER JOIN 

(SELECT cs.CompID, ic.CompeteID, MAX(IF(cast.CastType_ID BETWEEN 6 AND 12, cast.Length, 0)) + SUM(IF(cast.CastType_ID < 6, 40 - (cast.Length * 2), 0)) AS Casting 
FROM `comp-setup` cs 
    INNER JOIN `input-competitor` ic ON cs.CompID = ic.Comp_ID 
    LEFT JOIN `input-casting` cast ON cast.Compete_ID = ic.CompeteID 
    GROUP BY ic.CompeteID) Sub2

ON Sub1.CompeteID = Sub2.CompeteID
ORDER BY Total DESC

Here is the post with the example I needed: MYSQL LEFT JOIN with GROUP BY

mysql combined table joins

1 Answers1