1

A while ago, I asked for help on this SQL query. It shows snippets that belong to a parent category, using the category ID to tie them together. I tried to change it to show all categories, even when there's no snippets inside but I can't manage it.

I am sure this is not a PHP thing, as printing the result of this query does not show empty categories.

I just need help understanding this and what I need to change to make it show what I need it to.

Here's the SQL:

SELECT c.id AS cid, c.slug AS cslug, c.name AS cname, s.id AS sid, s.name AS sname 
FROM categories AS c 
LEFT JOIN snippets AS s ON s.category = c.id 
WHERE c.live=1 AND s.live=1 AND c.company='$company' AND s.company='$company' 
ORDER BY c.name, s.name

Any tips and links to good resources to learn SQL would be appreciated too. :)

Here's an example of what the query returns when run through PHP. SQL http://jsbin.com/obonal PHP Inefficient SQL Query

Community
  • 1
  • 1
PaulAdamDavis
  • 1,574
  • 3
  • 16
  • 19

3 Answers3

5

Move the snippet condition into the on clause of the join.

If you leave it in the where clause, result rows are required to match it, but missing rows from snippet will have null values, so s.live=1 will be false and you'll get no match.

In the on clause, only rows from snippet are required to match it, but with the left, matching is optional so you still get row from categories when snippet doesn't match:

SELECT c.id AS cid, c.slug AS cslug, c.name AS cname, s.id AS sid, s.name AS sname 
FROM categories AS c 
LEFT JOIN snippets AS s ON s.category = c.id AND s.live=1 AND s.company='$company' 
WHERE c.company='$company'
AND c.live=1 
ORDER BY c.name, s.name

The key part is the join on clause, which can be arbitrarily complex, and in the example of s.live=1 do not have to be true "join" predicates (ie don't have to compare values between tables - they can be just tests on the row itself):

LEFT JOIN snippets AS s ON s.category = c.id AND s.live=1 AND s.company='$company'

Note that some databases, eg mysql, allow your original syntax to still work as desired.

EDITED Moved s.company='$company' into the on clause too. Thanks @Andriy

Bohemian
  • 412,405
  • 93
  • 575
  • 722
  • 1
    I agree with with how you've summarised the core of the issue, but the WHERE clause in your query is in fact still containing a condition for the right-side table (`s.company='$company'`). – Andriy M Aug 06 '11 at 21:48
  • @Andriy Oh - didn't notice that. I'll move that in too! – Bohemian Aug 06 '11 at 22:45
  • Thanks, Bohemian and @Andriy. The query now works how I need it to and I know how! Cheers guys. – PaulAdamDavis Aug 06 '11 at 23:21
0

With RIGHT JOIN do you get the same result?

Esselans
  • 1,540
  • 2
  • 24
  • 44
0

If you want the perfect match you need an Inner Join. If you want all the rows from the left and all the rows from the right and left with the same id you need a Left Join.

Micromega
  • 12,486
  • 7
  • 35
  • 72