How to select from two tables in MySQL even if not all rows in one table have corespondents in the other?

by

To do this you need an outer join. By the way, the way you are writing your query with an implicit join is outdated and no longer recommended. Using the JOIN keyword is recommended. This also makes it easier to change an inner join to an outer join.

FROM categories AS sc
LEFT JOIN products AS s
ON s.ownerid=sc.id

To return 0 instead of NULL use IFNULL(..., 0). The entire query becomes:

SELECT
    sc.*,
    IFNULL(MIN(s.price), 0) AS minp,
    IFNULL(MAX(s.price), 0) AS maxp
FROM categories AS sc
LEFT JOIN products AS s
ON s.ownerid = sc.id
GROUP BY sc.id

You may also want to consider if it would be better to return the default NULL instead of 0 for categories that have no products.

More Related Contents:

  1. How can I select data from different tables?
  2. What’s the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN? [duplicate]
  3. How can I do three table JOINs in an UPDATE query?
  4. 1052: Column ‘id’ in field list is ambiguous
  5. MySQL: Quick breakdown of the types of joins [duplicate]
  6. Join between tables in two different databases?
  7. MySQL JOIN ON vs USING?
  8. Required to join 2 tables with their FKs in a 3rd table
  9. MySQL join with where clause
  10. JOIN queries vs multiple queries
  11. Why does MySQL report a syntax error on FULL OUTER JOIN?
  12. CROSS/OUTER APPLY in MySQL
  13. Mixing ANSI 1992 JOINs and COMMAs in a query
  14. Cumulative sum over a set of rows in mysql
  15. MySql Count cannot show 0 values
  16. What’s the difference between comma separated joins and join on syntax in MySQL? [duplicate]
  17. MySQL JOIN the most recent row only?
  18. MySQL update a joined table
  19. MySQL join query using like?
  20. MySQL JOIN with LIMIT 1 on joined table
  21. Difference between these two joining table approaches?
  22. Laravel join with 3 Tables
  23. MySQL Insert & Joins
  24. mySQL – Create a New Table Using Data and Columns from Three Tables
  25. Count with IF condition in MySQL query
  26. How to join two tables using a comma-separated-list in the join field
  27. Mysql select where not in table
  28. When to use a left outer join?
  29. In what order are MySQL JOINs evaluated?
  30. How to store a one to many relation in MySQL database?

Leave a Comment