Re: query help

Jim C. Nasby wrote:
> On Wed, Jul 26, 2006 at 01:37:05PM +0200, Brendon Gleeson wrote:
>
>>I have a table called "marketing_campaigns":
>>
>>marketing_campaigns:
>>+----+--------------+------------+-------------+-------------
>>| id | date_started | date_ended | property_id | status
>>+----+--------------+------------+-------------+-------------
>>| 1  |  2005-01-01  | 2005-03-12 |  5          | sold
>>| 2  |  2006-01-11  | 2006-02-23 |  5          | jointly sold
>>| 3  |  2006-05-13  | NULL       |  5          | for sale
>>| 4  |  2006-02-01  | 2006-02-06 |  6          | sold
>>
>>I am having trouble trying to say: Only show old campaigns And only if
>>their is not a "current" campaign for this property(property_id)
>
>
> #define 'old campaigns' and 'current'
>
>
>>The result of the query should only return row 4. Is their a way to do this
>>in sql without resorting to proprietary functions, or should I resort to
>>using Ruby (rails app)
>
>
> No, this can certainly be done in SQL, though if you have pseudo or ruby
> code on how you'd do it, it might make it a bit more clear on what
> you're after.

I can't test this at the moment but how about something like this.

SELECT *
   FROM marketing_campaigns
  WHERE date_ended IS NOT NULL
    AND property_id NOT IN ( SELECT property_id
                               FROM marketing_campaigns
                              WHERE date_ended IS NULL
                            )
  ORDER BY id;

--

Kind Regards,
Keith