I have a table which has the following columns.

id - int(11)
catid - int(11)
title - varchar(60)
content - text
parent - int(11)
postdate - datetime
user - int(11)
view - int(11)
email - char(1)
emailed - char(1)

Here's what I'm trying to accomplish.

This table is for a forum. If a user posts a question and selects to be automatically email the email column will be set to 'Y'

So when a user responds to the post the emailed column will be 'Y'

Every hour or so I will do a cron job to send out an email to the original poster that he/she has a reply to their post.

I want to get a list of those id's that have email 'Y' and emailed = 'Y'

I can get those queries separtely below, but want to do it in one. how can I accomplish this...

my queries.

SELECT * FROM forum_tbl where parent = 0 and email = 'Y' // original post where user wants emails

SELECT * FROM forum_tbl where parent > 0 and emailed = 'N' // reply to post where email has not been sent.

I'm using MySQL 4.0.20a-max

Thank you.

You can use the UNION of both selects to get the result set:

select .....
UNION
select .....

or you can use

SELECT *
FROM
forum_tbl
WHERE
(parent = 0 and email = 'Y') OR (parent > 0 and emailed = 'N')

You can use the UNION of both selects to get the result set:

select .....
UNION
select .....

or you can use

SELECT *
FROM
forum_tbl
WHERE
(parent = 0 and email = 'Y') OR (parent > 0 and emailed = 'N')

The outcome puts the two selects together but I need to eliminate some of the information, like an intersect, but I can't do that in MySQL.

The first select has the ID I want. The second had the parent ID.

I need to match only those.

Make sense?

Thanks.

Can you post an examle of what you want done? a few records in the table and the result set you are looking for, maybe that way I can help out better.

id catid title content parent postdate user views email emailed
19 3 test test con 0 2004-12-07 00:00:00 1 3 Y NULL

24 2 test again 0 2004-01-08 12:52:04 1 11 Y NULL
25 2 test ing 24 2004-12-08 00:00:00 1 0 NULL N

This is the outcome of the union your posted earlier.

The outcome I'm looking for is that it only should show id 24 nothing else because it is the only one that has a reply that has not been "emailed".

Thanks.