Don't like ads? PRO users don't see any ads ;-)
Public Pastes
Guest

Untitled

By: a guest on May 31st, 2012  |  syntax: None  |  size: 2.05 KB  |  hits: 15  |  expires: Never
download  |  raw  |  embed  |  report abuse  |  print
Text below is selected. Please press Ctrl+C to copy to your clipboard. (⌘+C on Mac)
  1. mysql - two left joins - double counting
  2. table1
  3. id
  4.  
  5. table2
  6. id | val (each id has 1 of 3 possible values)
  7.  
  8. table3
  9. id | val (each id has 1 of 3 possible values)
  10.        
  11. SELECT
  12.     count(DISTINCT table1.id) AS x1,
  13.     SUM(IF(table2.val='1'),1,0)) AS x2,
  14.     SUM(IF(table2.val='2'),1,0)) AS x3,
  15.     SUM(IF(table2.val='3'),1,0)) AS x4,
  16.     SUM(IF(table3.val='1'),1,0)) AS x5,
  17.     SUM(IF(table3.val='2'),1,0)) AS x6,
  18.     SUM(IF(table3.val='3'),1,0)) AS x7
  19. FROM
  20.     table1
  21. LEFT JOIN
  22.     table2 ON table1.id=table2.id
  23. LEFT JOIN
  24.     table3 ON table1.id=table3.id
  25.        
  26. SELECT
  27.     count(view1.id)              AS x1,
  28.     SUM(IF(table2.val='1'),1,0)) AS x2,
  29.     SUM(IF(table2.val='2'),1,0)) AS x3,
  30.     SUM(IF(table2.val='3'),1,0)) AS x4,
  31.     SUM(IF(table3.val='1'),1,0)) AS x5,
  32.     SUM(IF(table3.val='2'),1,0)) AS x6,
  33.     SUM(IF(table3.val='3'),1,0)) AS x7
  34. FROM
  35.     (  SELECT DISTINCT table1.id
  36.        FROM   table1
  37.     ) view1
  38. LEFT JOIN
  39.     table2 ON view1.id=table2.id
  40. LEFT JOIN
  41.     table3 ON view1.id=table3.id
  42.        
  43. SELECT
  44.       SumForTable1.x1,
  45.       SumForTable2.x2,
  46.       SumForTable2.x3,
  47.       SumForTable2.x4,
  48.       SumForTable3.x5,
  49.       SumForTable3.x6,
  50.       SumForTable3.x7
  51.    FROM
  52.       ( select count(DISTINCT table1.id) AS x1
  53.            from table1 ) SumForTable1,
  54.  
  55.       ( select SUM(IF(table2.val='1'), 1, 0)) AS x2,
  56.                SUM(IF(table2.val='2'), 1, 0)) AS x3,
  57.                SUM(IF(table2.val='3'), 1, 0)) AS x4
  58.             from table2 ) SumForTable2,
  59.  
  60.       ( select SUM(IF(table3.val='1'), 1, 0)) AS x5,
  61.                SUM(IF(table3.val='2'), 1, 0)) AS x6,
  62.                SUM(IF(table3.val='3'), 1, 0)) AS x7
  63.             from table3 ) SumForTable3
  64.        
  65. SELECT
  66.     count(t1.id) AS t1,
  67.     SUM(IF(t2.val=1,1,0)) AS t21,
  68.     SUM(IF(t2.val=2,1,0)) AS t22,
  69.     SUM(IF(t2.val=3,1,0)) AS t23,
  70.     SUM(IF(t3.val=1,1,0)) AS t31,
  71.     SUM(IF(t3.val=2,1,0)) AS t32,
  72.     SUM(IF(t3.val=3,1,0)) AS t33
  73. FROM (SELECT DISTINCT * FROM table1) as t1
  74. JOIN (SELECT DISTINCT * FROM table2) as t2 ON t1.id=t2.id
  75. JOIN (SELECT DISTINCT * FROM table3) as t3 ON t1.id=t3.id;
create a new version of this paste RAW Paste Data