Results 1 to 6 of 6

Thread: Combine and

  1. #1
    Join Date
    Oct 2003
    Location
    santa clara
    Posts
    25

    Unanswered: Combine and

    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.

  2. #2
    Join Date
    Aug 2004
    Posts
    99
    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')
    Last edited by lgaxiola; 12-09-04 at 16:22.

  3. #3
    Join Date
    Aug 2004
    Posts
    99
    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')

  4. #4
    Join Date
    Oct 2003
    Location
    santa clara
    Posts
    25

    Not quite what I'm looking for

    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.

  5. #5
    Join Date
    Aug 2004
    Posts
    99
    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.

  6. #6
    Join Date
    Oct 2003
    Location
    santa clara
    Posts
    25
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •