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

Untitled

By: a guest on Jul 15th, 2012  |  syntax: None  |  size: 1.51 KB  |  hits: 14  |  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. Get all users even if no records in another table
  2. +----+---------------+----------+
  3. | id | username      | company  |
  4. +----±---------------±----------+
  5. | 1  | John          | 0        |
  6. | 2  | Jack          | 0        |
  7. | 3  | Casimir       | 0        |
  8. ±----±---------------±----------±
  9.        
  10. +----+---------------+----------+--------+
  11. | id | date          | iduser   | status |
  12. +----±---------------±----------+--------+
  13. | 1  | 2012-05-28    | 1        | 1      |
  14. | 2  | 2012-05-25    | 1        | 1      |
  15. | 3  | 2012-04-28    | 2        | 1      |
  16. | 4  | 2012-03-28    | 1        | 1      |
  17. | 5  | 2012-02-28    | 2        | 0      |
  18. ±----±---------------±----------±--------+
  19.        
  20. +----------+---------------+-------------+
  21. | username | COUNT(order)  | MAX(date)   |
  22. +----------±---------------±-------------+
  23. | John     | 3             | 2012-05-28  |
  24. | Jack     | 1             | 2012-04-28  |
  25. | Casimir  | 0             | NULL        |
  26. ±----------±---------------±-------------±
  27.        
  28. SELECT u.username, COUNT(o.id), MAX(o.date)
  29. FROM users u
  30. INNER JOIN orders ON u.id = o.iduser
  31. WHERE o.status = 1
  32. GROUP BY u.id
  33.        
  34. +----------+---------------+-------------+
  35. | username | COUNT(order)  | MAX(date)   |
  36. +----------±---------------±-------------+
  37. | John     | 3             | 2012-05-28  |
  38. | Jack     | 1             | 2012-04-28  |
  39. ±----------±---------------±-------------±
  40.        
  41. SELECT u.username, COUNT(o.id), MAX(o.date)
  42. FROM users u
  43. LEFT OUTER JOIN orders o ON u.id = o.iduser AND o.status = 1
  44. GROUP BY u.id
create a new version of this paste RAW Paste Data