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)
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)
I have been using the following solution, however it is probably not the
best way to do things:
"SELECT property_id, address FROM properties
LEFT JOIN marketing_campaigns
ON marketing_campaigns.property_id = properties.id
WHERE ended_on IS NOT NULL AND address LIKE #{SEARCHSTRING}
AND property_id NOT IN (
SELECT property_id FROM marketing_campaigns
WHERE ended_on IS NULL ORDER BY property_id)
GROUP BY property_id, address"