I have a docket, which has many entries. I want only dockets, where none of the associating entries contains the text "PENDING". For example, if docket A has 10 entries, and one of those entries' docket_type varchar field contains the word "PENDING" anywhere, then I don't want that docket.

This is my solution:

SELECT * FROM dockets
INNER JOIN entries 
ON entries.docket_id = docket.id 
WHERE (docket_type NOT LIKE '%PENDING%')

The problem is, while it does not retrieve entries that contain that text, it still retrieves the associating dockets, when those dockets contain other entries that do not contain that text. I don't want to retrieve the docket if one of its entries contains this text in docket_type.

How could I fix this?

  • Do you want to keep the entries (without "pending") in the SELECT list? – ypercubeᵀᴹ Jun 10 '15 at 1:29
  • @ypercube yes but if an entry does have "pending", I don't want the docket either. – Donato Jun 10 '15 at 1:30

You need one more join (a LEFT join with IS NULL check) or a NOT EXISTS subquery and to move the condition (reversed!) there:

SELECT * FROM dockets
INNER JOIN entries 
ON entries.docket_id = docket.id 
      ( SELECT 1
        FROM entries AS e
        WHERE e.docket_id = docket.id 
          AND e.docket_type LIKE '%PENDING%'
      ) ;
| improve this answer | |
  • Can I get a brief explanation of why you use the number 1 in "SELECT 1"? – Donato Jun 10 '15 at 1:47
  • 1
    You can use whatever you like. People often use SELECT * or SELECT 1 or SELECT NULL in exists and not exists subqueries. The exists means that the subquery returns a boolean value, either true or false, dependent on when the subquery has rows. So, the select list doesn't really matter. – ypercubeᵀᴹ Jun 10 '15 at 1:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.