$15

Cartesian product?

SELECT s.name Store, c.stores_id 'Store ID', sp.name 'Partner Site', count(c.id), c.who_told_them, sc.cost_per_click CPC, FORMAT(sum(c.revenue),2) Revenue 

FROM buys c LEFT JOIN stores s ON (c.stores_id=s.id) 

LEFT JOIN stores_cpcs sc ON (s.id=sc.stores_id) 

LEFT JOIN spn_partners sp ON (c.who_told_them=sp.who_told_them)

WHERE c.status = 'VALID' 

AND c.started BETWEEN '2011-05-01' AND '2011-05-02 23:59:59'

AND c.who_told_them='specials'

GROUP BY c.stores_id, c.who_told_them

saucer | 05/03/11 at 1:02pm | Edit

Previous versions of this question: 05/03/11 at 1:32pm | 05/03/11 at 1:44pm

(2) Possible Answers Submitted...

• 
  Last edited:
  05/18/11
  2:36pm
  John Cotton says:

  Should those be LEFT JOINS ?
  
  Try the same query but with INNER JOIN in the place of each LEFT JOIN.
  
  You'll need to add the other fields to your GROUP BY or you'll get an error, but assuming that they are always the same, it won't make a difference.

  Previous versions of this answer: 05/03/11 at 1:08pm

  • 05/03/11 1:35pm
    
    John Cotton says:

    Apparently the problem is this line:
    
    LEFT JOIN stores_cpcs sc ON (s.id=sc.stores_id)
    
    If I take that out all is well. This might have something to do with sc having a 3 column index instead of 1?

    
    
    So try changing that to

     LEFT JOIN stores_cpcs sc ON (s.id=sc.stores_id AND s.col_2 = sc.col_2 AND s.col_3 = sc.col_3) 

    
    
    where col_2 and col_3 are your 2nd and 3rd column indexes.

  • 05/03/11 1:41pm
    
    John Cotton says:

    It's difficult to know precisely what to tell you without seeing what is clearly a complicated table structure.
    
    So some advice instead.
    
    Try taking about the GROUP BY and COUNT/SUM elements and just get rows back for now.
    
    Once you have the right number of rows from your plain SQL statement, but the GROUP BY and SUM back in to get the desired output.

  • 05/03/11 1:47pm
    
    John Cotton says:

    http://dev.mysql.com/doc/refman/5.0/en/multiple-column-indexes.html

    
    
    Sorry, I thought when you said index you meant key.....!
    
    Since you really meant index then the index has nothing to do with your problem.
    
    It's the LEFT JOIN that's the issue - you're joining to the outer rows even when no join exists - you're just getting NULL back from the column values.
    
    Make that an INNER JOIN as I first suggested and it should work.

• 
  Last edited:
  05/18/11
  2:36pm
  Muhammad Wasif says:

  Looks like a problem with
  GROUP BY c.stores_id, c.who_told_them
  
  Because there will be multiple values of who_told_them. To get all the values of who_told_them against a single store, use GROUP_CONCAT() in this way
  
  

  SELECT s.name Store, c.stores_id 'Store ID', sp.name 'Partner Site', count(c.id), GROUP_CONCAT(c.who_told_them) who_told, sc.cost_per_click CPC, FORMAT(sum(c.revenue),2) Revenue 
  
  FROM buys c LEFT JOIN stores s ON (c.stores_id=s.id) 
  
  LEFT JOIN stores_cpcs sc ON (s.id=sc.stores_id) 
  
  LEFT JOIN spn_partners sp ON (c.who_told_them=sp.who_told_them)
  
  WHERE c.status = 'VALID' 
  
  AND c.started BETWEEN '2011-05-01' AND '2011-05-02 23:59:59'
  
  AND c.who_told_them='specials'
  
  GROUP BY c.stores_id, c.who_told_them

This question has expired.

Current status of this question: Completed

Top Experts (+)

• 
  Jarret Minkler$117 earned (plus $0)
• 
  William Clark$77 earned (plus $0)
• 
  Oleg Butuzov$60 earned (plus $0)
• 
  Steven Siebert$20 earned (plus $0)
• 
  Russell Smith$16 earned (plus $70)

Top Donors (+)

Top Supporters (+)