SitePoint Sponsor

User Tag List

Results 1 to 13 of 13
  1. #1
    SitePoint Wizard WorldNews's Avatar
    Join Date
    Nov 2007
    Posts
    1,033
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    What is the best method to achieve this complex objective

    Hi,

    Here is what I want to do:

    1st- Look in various tables and get email records from each table based on the selection criteria - this is Done

    2nd- Merge all these various email lists which have been returned by the many different MySQL selects into one master list. Note: all the select commands return 'email'

    3rd- If the selection was made by the Admin that the email list should contain distinct emails only, then make sure that the list of emails are distinct. Keeping in mind that these emails are coming from many different tables.

    Is there a MySQL or Php command(s) that can meet objectives 2 & 3?

    Regards,

    Anoox search engine volunteer

    www.anoox.com

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    yes, there is, it's called a UNION query

    it will do 1 as well as 2 and 3
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard WorldNews's Avatar
    Join Date
    Nov 2007
    Posts
    1,033
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Hi rudy,

    can you give me an actual example.
    So lets say I have:

    this code:

    if ($unique == 'yes') {

    $count_emails = 'COUNT(distinct(email))';
    $email_list = 'distinct(email)';


    } else {

    $count_emails = 'COUNT(email)';
    $email_list = 'email';

    }

    $date_filter = "submited_date BETWEEN " . $start . " AND " . $end;

    $sql_count_not_conf = "select $email_list FROM x1 WHERE $date_filter";

    $query_count_not_conf = mysql_query($sql_count_not_conf) or die(mysql_error());

    $result_count_not_conf = mysql_fetch_array($query_count_not_conf);


    $sql_count_conf = "select $email_list FROM x2 WHERE $date_filter";

    $query_count_conf = mysql_query($sql_count_conf) or die(mysql_error());

    $result_count_conf = mysql_fetch_array($query_count_conf);


    How do I join the email list returned by above 2 Sql queries?

    And how do I make sure that the resulting joined email list will remain
    distinct, that is if distinct was turned on.

    Regards,

    BTW, I did but your MySQL book

    Quote Originally Posted by r937 View Post
    yes, there is, it's called a UNION query
    it will do 1 as well as 2 and 3

    Anoox search engine volunteer

    www.anoox.com

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    sorry, i don't do perl or whatever that language is, but i can give you pseudocode for the UNION query which will allow you to have distinct or not distinct results...
    Code:
    SELECT email, name FROM table1 WHERE somecondition
    UNION if ($unique == 'yes') { } else { ALL }
    SELECT email, name FROM table2 WHERE somecondition
    ORDER BY email
    and of course you will look up in da manual what is the difference between UNION and UNION ALL, yes?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Wizard WorldNews's Avatar
    Join Date
    Nov 2007
    Posts
    1,033
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Hi rudy,

    1st, that language is Php.

    Now about your comment:

    1- Is the code that you have provided all MySQL?
    That is even the if ($unique == 'yes') { ALL } part.

    2- So UNION by default makes the list to be distinct (aka $unique == 'yes')
    unless told otherwise via the ALL option?

    Regards,

    Quote Originally Posted by r937 View Post
    sorry, i don't do perl or whatever that language is, but i can give you pseudocode for the UNION query which will allow you to have distinct or not distinct results...
    Code:
    SELECT email, name FROM table1 WHERE somecondition
    UNION if ($unique == 'yes') { } else { ALL }
    SELECT email, name FROM table2 WHERE somecondition
    ORDER BY email
    and of course you will look up in da manual what is the difference between UNION and UNION ALL, yes?


    Anoox search engine volunteer

    www.anoox.com

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by WorldNews View Post
    1st, that language is Php.
    oh, that's nice... but some of us don't know the difference between php and perl

    Quote Originally Posted by WorldNews View Post
    1- Is the code that you have provided all MySQL?
    That is even the if ($unique == 'yes') { ALL } part.
    no

    the "if" part was pseudocode

    Quote Originally Posted by WorldNews View Post
    2- So UNION by default makes the list to be distinct (aka $unique == 'yes')
    unless told otherwise via the ALL option?
    that is correct


    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Wizard WorldNews's Avatar
    Join Date
    Nov 2007
    Posts
    1,033
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Hi Rudy,

    1st, Thanx I got this working Ok.
    That is now I am able to generate a master list of emails from selecting from multiple tables with UNION [ALL].

    Next and last I need to do something else to be done with this list generation:

    I need to cross check this generated master list of emails with a Table, lets call it PL, and remove from the generated master list of emails any emails that are also in the PL table for a selected date range.

    Regards,


    Quote Originally Posted by r937 View Post
    oh, that's nice... but some of us don't know the difference between php and perl

    no

    the "if" part was pseudocode

    that is correct



    Anoox search engine volunteer

    www.anoox.com

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    sounds like you want to do a joined delete

    have a look in da manual and see if you can find the appropriate example which shows you how to do that
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Wizard WorldNews's Avatar
    Join Date
    Nov 2007
    Posts
    1,033
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    sounds like you want to do a joined delete

    I dont think so.
    But maybe.

    have a look in da manual and see if you can find the appropriate example which shows you how to do that
    Well if I could have found the answer in "da manual" I would not have posted a request for help here.
    Thank anyway.

    Anoox search engine volunteer

    www.anoox.com

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    wait! wait! i was wrong! sorry! you don't want DELETE at all!

    try this --
    Code:
    SELECT * FROM ( put the entire UNION query here ) AS u
    WHERE email NOT IN ( SELECT email FROM PL )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Wizard WorldNews's Avatar
    Join Date
    Nov 2007
    Posts
    1,033
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Thanks, this seems to do the Job

    But I am just curious, what is the role of that AS u?
    Seems to have no purpose!
    I mean should it not be u.email at least?

    Quote Originally Posted by r937 View Post
    wait! wait! i was wrong! sorry! you don't want DELETE at all!
    try this --
    Code:
    SELECT * FROM ( put the entire UNION query here ) AS u
    WHERE email NOT IN ( SELECT email FROM PL )

    Anoox search engine volunteer

    www.anoox.com

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by WorldNews View Post
    But I am just curious, what is the role of that AS u?
    Seems to have no purpose!
    try leaving it out and you will get an error message
    The [AS] name clause is mandatory, because every table in a FROM clause must have a name.

    -- http://dev.mysql.com/doc/refman/5.0/...med-views.html
    Quote Originally Posted by WorldNews View Post
    I mean should it not be u.email at least?
    it can be but it doesn't need to be, since it's unambiguous because there is only one table in the outer query's FROM clause

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    SitePoint Wizard WorldNews's Avatar
    Join Date
    Nov 2007
    Posts
    1,033
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Ok, thanx anyway.

    But I must admit I have looked at that simple looking statement over and over
    and cannot figure out how it is doing its Job!


    Quote Originally Posted by r937 View Post
    try leaving it out and you will get an error message
    it can be but it doesn't need to be, since it's unambiguous because there is only one table in the outer query's FROM clause


    Anoox search engine volunteer

    www.anoox.com


Bookmarks

Posting Permissions

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