Include `WHERE` in a joined sql sequence

Question

I have the following working sql sequence:

SELECT *, films.category AS filmCategory
FROM ( SELECT *
       FROM films
       ORDER BY `unique` ASC
       LIMIT 6, 4) films
LEFT OUTER JOIN items ON items.unique = films.ref
ORDER BY films.unique ASC

This works well and selects the correct four elements from the DB. However, I have some rules that I check for using WHERE, that I can't get working. I have done the following:

SELECT *, films.category AS filmCategory
FROM ( SELECT *
       FROM films
       ORDER BY `unique` ASC
       LIMIT 6, 4) films
LEFT OUTER JOIN items ON items.unique = films.ref
WHERE films.youtube IS NOT NULL AND films.youtube <> ''
ORDER BY films.unique ASC

where the only difference is the added line with the WHERE clause. But this doesn't work - in fact it makes no difference from before but returns the same rows.

How can I include these WHERE rules correctly in this sql sentence?

Note

The line films.youtube IS NOT NULL AND films.youtube <> '' is checking if a specific cell is empty. This is made with help from this question

In what way is it not working? Does it return rows that have NULL or "empty" values of `films.youtube`? — BellevueBob, Jun 30 '13 at 16:15

Andomar · Accepted Answer · 2013-06-30T16:24:35.010

Perhaps you are looking for a where clause in the subquery? That way, the limit will be applied after your where clause.

SELECT *, films.category AS filmCategory
FROM ( SELECT *
       FROM films
       WHERE films.youtube IS NOT NULL AND films.youtube <> ''
       ORDER BY `unique` ASC
       LIMIT 6, 4) films
LEFT OUTER JOIN items ON items.unique = films.ref
ORDER BY films.unique ASC

A small additional suggestion. You can simplify:

WHERE films.youtube IS NOT NULL AND films.youtube <> ''

to

WHERE films.youtube > ''

because null > '' is not true (but unknown.) Or perhaps more readable:

WHERE length(films.youtube) > 0

Even simpler: `WHERE films.youtube > ''` – ypercubeᵀᴹ Jun 30 '13 at 16:19 — ypercubeᵀᴹ, Jun 30 '13 at 16:19

Include `WHERE` in a joined sql sequence

1 Answers1