excluding records according to expiry date

I need some help achieving the desired recordset. I suspect a nested select
might be the solution, but I am unsure of the syntax.

I have 3 tables:

table #1: node
field: nid, int(11)
field: type, varchar(32)
field: created, int(11) -- unix_timestamp

table #2: votingapi_vote
field: content_id, int(11) -- foreign key to node.nid
field: value, int(2) (always equals '1')

table #3: node_promotion
field: nid, init(11) -- foreign key to node.nid
field: promoted, int(11) -- unix_timestamp

Using the following query, I need to return 6 records:

SELECT n.nid, Count( v.value ) AS points, np.promoted
FROM node n
LEFT JOIN votingapi_vote v ON v.content_id = n.nid
AND v.value_type = 'points'
LEFT JOIN node_promotion np ON np.nid = n.nid
AND np.promoted > UNIX_TIMESTAMP( ) -172800
WHERE n.type = 'product'
GROUP BY n.nid
ORDER BY points DESC, n.created DESC
LIMIT 6

If the np.promoted datestamp is newer than 48 hours (172800 seconds) it will
include those records. For example:

nid points promoted

1204 2 1173400123
1190 2 1173300123
1225 1 1173200123
1202 1 1173100123
1200 1 1173000123
1229 0 NULL

Note the last record doesn't exist in node_promotion table and therefore
doesn't have any corresponding points and promoted date.

When the records begin to expire (the promoted date is already than 48
hours) and no newer records exist, I need to query to simply return 6
records from the node table with 0 points and the promoted date set to null.
For example:

1229 0 NULL
1228 0 NULL
1227 0 NULL
1226 0 NULL
1224 0 NULL
1199 0 NULL

Here's my dilemma. The current query will still return the same 6 records
even if the promoted date equals null. For example

1204 2 1173400123
1190 2 1173300123
1225 1 NULL
1202 1 NULL
1200 1 NULL
1229 0 NULL

If a promoted date is older than 48 hours, I need exclude the records with
points altogether. So using the data from the above examples the current 6
would be:

1204 2 1173400123
1190 2 1173300123
1229 0 NULL
1228 0 NULL
1227 0 NULL
1226 0 NULL

Any advice would be greatly appreciated. Please let me know if my data or
examples is not clear enough.

Thanks.

"keychain" <somebody@somewhere.com> wrote in message
news:MIOdnetVf68WpW_YnZ2dnUVZ_vShnZ2d@comcast.com. ..
>I need some help achieving the desired recordset. I suspect a nested select
>might be the solution, but I am unsure of the syntax.
>
> I have 3 tables:
>
> table #1: node
> field: nid, int(11)
> field: type, varchar(32)
> field: created, int(11) -- unix_timestamp
>
> table #2: votingapi_vote
> field: content_id, int(11) -- foreign key to node.nid
> field: value, int(2) (always equals '1')
>
> table #3: node_promotion
> field: nid, init(11) -- foreign key to node.nid
> field: promoted, int(11) -- unix_timestamp
>
> Using the following query, I need to return 6 records:
>
> SELECT n.nid, Count( v.value ) AS points, np.promoted
> FROM node n
> LEFT JOIN votingapi_vote v ON v.content_id = n.nid
> AND v.value_type = 'points'
> LEFT JOIN node_promotion np ON np.nid = n.nid
> AND np.promoted > UNIX_TIMESTAMP( ) -172800
> WHERE n.type = 'product'
> GROUP BY n.nid
> ORDER BY points DESC, n.created DESC
> LIMIT 6
>
> If the np.promoted datestamp is newer than 48 hours (172800 seconds) it
> will include those records. For example:
>
> nid points promoted
>
> 1204 2 1173400123
> 1190 2 1173300123
> 1225 1 1173200123
> 1202 1 1173100123
> 1200 1 1173000123
> 1229 0 NULL
>
> Note the last record doesn't exist in node_promotion table and therefore
> doesn't have any corresponding points and promoted date.
>
> When the records begin to expire (the promoted date is already than 48
> hours) and no newer records exist, I need to query to simply return 6
> records from the node table with 0 points and the promoted date set to
> null. For example:
>
> 1229 0 NULL
> 1228 0 NULL
> 1227 0 NULL
> 1226 0 NULL
> 1224 0 NULL
> 1199 0 NULL
>
> Here's my dilemma. The current query will still return the same 6 records
> even if the promoted date equals null. For example
>
> 1204 2 1173400123
> 1190 2 1173300123
> 1225 1 NULL
> 1202 1 NULL
> 1200 1 NULL
> 1229 0 NULL
>
> If a promoted date is older than 48 hours, I need exclude the records with
> points altogether. So using the data from the above examples the current 6
> would be:
>
> 1204 2 1173400123
> 1190 2 1173300123
> 1229 0 NULL
> 1228 0 NULL
> 1227 0 NULL
> 1226 0 NULL
>
> Any advice would be greatly appreciated. Please let me know if my data or
> examples is not clear enough.
>
> Thanks.
>
>
>

This is what the query for the above scenario ended up looking like:

SELECT *
FROM
(SELECT n.nid, Count( v.value ) AS points, np.promoted
FROM node n
LEFT JOIN votingapi_vote v ON v.content_id = n.nid
AND v.value_type = 'points'
LEFT JOIN node_promotion np ON np.nid = n.nid
WHERE n.type = 'product'
GROUP BY n.nid
ORDER BY points DESC , n.created DESC)
AS nodes
WHERE promoted > UNIX_TIMESTAMP( ) -172800 OR promoted IS NULL
LIMIT 6

I nested the original query and moved the datestamp condition to the outer
WHERE clause. This, along with a condition to include records with
'promoted' equaling NULL, give me my 6 needed records. Note that only 6
records are set as 'promoted' at any given time so worst case the query
returns only the 6 records where promoted equals NULL.

I'd be curious to know if this query could be further optimized or written
more efficiently.