1

I'm looking for solution opposite this question. I want to self-join tableA as tableB so where clause would not affect tableB result. I want to get sum of all customers and sum of customers with cancel_date='0000-00-00' I don't want to switch the places of the tables tableA and tableB. I do have other where and select statements based on tableA. 

SELECT 
count(tableA.client_id) as c_total, 
count(tableB.client_id) as all_c_total 
FROM (tableA) LEFT JOIN tableA tableB ON tableA.client_id = tableB.client_id
WHERE tableA.`cancel_date` = '0000-00-00' 
GROUP BY month(tableA.purch_date) 
ORDER BY month(tableA.purch_date)

The result should looks like this:

---------------------
c_total | all_c_total
---------------------
  251   | 273
  45    | 65
  12    | 15
  23    | 29
Community
  • 1
  • 1
Sergey Tsibel
  • 1,595
  • 1
  • 18
  • 31

1 Answers1

0

You could use a RIGHT JOIN, and move the conditional out of the WHERE clause into the join condition...

SELECT 
    count(t1.client_id) as c_total, 
    count(t2.client_id) as all_c_total 
FROM tableA t1 
    RIGHT JOIN tableA t2 
        ON t1.client_id = t2.client_id
        AND t1.`cancel_date` = '0000-00-00' 
GROUP BY month(t1.purch_date) 
ORDER BY month(t1.purch_date)

But... I'd suggest you reconsider, switch the order of the tables, and use a LEFT JOIN instead. LEFT JOIN is considered better style and more readable than RIGHT JOIN.

Michael Fredrickson
  • 36,839
  • 5
  • 92
  • 109
  • all my select and where statements are based on tableA, so I considered that it would inappropriate switch tables up side down just because of the one extra column from tableB (all_c_total). And your answer didn't work ( cancel_date still affects both tables) – Sergey Tsibel Aug 01 '12 at 21:10
  • @castt Hmmm... I've updated my answer to alias both tables... `cancel_date` shouldn't limit the results for `t2`, so if the updated query doesn't give you the expected results, there must be some other issue, in which case I would suggest posting sample data to go along with the expected results you provided. – Michael Fredrickson Aug 01 '12 at 21:16
  • @castt There should be no conditions in your `where` clause for `tableA t1`... this would turn your `outer join` into an `inner join`. As far as the ordering of your tables in your `from` clause, I don't think this should be determined by which table has the most columns in your `select` statement... instead I think the ordering should be based on some sort of logical progression, starting with the required records (`t2`) and progressing to the optional records (`t1`).. – Michael Fredrickson Aug 01 '12 at 21:19
  • I see your point, but what if I want to have 3 'where' clause for both tables and one of those 'where' statements should not include t2? Does it make sense? Or my query-structure is wrong? – Sergey Tsibel Aug 01 '12 at 23:12