Help with a Join query with null rows

RGM · #1 (**permalink**) 07-08-11, 00:03

I'm trying to get records from the left table where they don't have a corrosponding record in the right table.

The scenario is:
events expire and when they do, a notification should be set, but only once.

So, I'm trying to find events with a type_id of 2 that have expired from the left table and join to the right table to find if they already have a message of type 3.

If they don't, I want the event row. If they do, I don't want anything.

My query so far:

Code:

SELECT c.id
FROM SA_causes c
LEFT OUTER
JOIN SA_feeds_causes fc
on fc.cause_id = c.id
WHERE c.type_id = 2 
AND fc.id is null 
AND c.end_date >= "2011-07-08 15:56:24" 
AND fc.type_id = 3

Thanks for any help

r937 · #2 (**permalink**) 07-08-11, 02:14

Code:

SELECT c.id
  FROM SA_causes c
LEFT OUTER
  JOIN SA_feeds_causes fc
    ON fc.cause_id = c.id
   AND fc.type_id = 3
 WHERE c.type_id = 2 
   AND c.end_date >= '2011-07-08 15:56:24' 
   AND fc.id IS NULL

RGM · #3 (**permalink**) 07-08-11, 02:37

Uh, can't believe that cost me hours.

Thank you Rudy.

Internet Services

Web Development

Digital Marketing

Consumer Tech