#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2009
    Posts
    75
    Rep Power
    6

    Select rows which have no match in other table?


    I have this structure
    tbl_site_urls: storing urls of a site
    tbl_camp_urls: storing urls of a campaign, after handling these urls
    tbl_site_urls
    id - site_id - url
    1 - 181 - url1
    2 - 182 - url2
    3 - 189 - url3

    tbl_camp_urls
    id - camp_id - site_id - url
    1 - 5 - 181 - url1


    if i want to list the urls need to be handle

    select url from tbl_site_urls
    join site on tbl_site_urls.site_id = site.id AND site.valid=1

    then in a loop operation i insert the handled urls into the tbl_camp_urls

    I want to list the urls that which have not handled yet.
    it is like a comparison operation.


    The results I'm expecting is
    2 - 182 - url2
    3 - 189 - url3
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2012
    Posts
    95
    Rep Power
    3
    there are 2 ways to do this. you should experiment to see which is more efficient with your tables (usually it's the 2nd one)

    1.
    SELECT url FROM tbl_site_urls
    WHERE tbl_site_urls.site_id NOT IN (SELECT site.id FROM site)
    2.
    SELECT url FROM tbl_site_urls
    LEFT JOIN site ON tbl_site_urls.site_id=site.id WHERE site.id IS NULL

IMN logo majestic logo threadwatch logo seochat tools logo