Negative SQL Query

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • abighill
    #1

    Negative SQL Query

    Problem
    -------------

    I want to return all URL records from 'fett_url' that are not currently
    indexed in the lookup table 'fett_url_to_da ta' where 'data_id=2'.

    i.e.

    fields=> url_id, url_title, url_link, url_description

    "1","Flickr","h ttp://flickr.com","Ph oto tool"

    Previously I have managed to make a Positive SQL Query (see below)
    when I try to reverse this including "!=" the results are not what I
    require (See Negative SQL Query).

    Any ideas would be greatly appreciated.


    Project Fett Data Stucture
    -----------------------------------------

    table=> fett_url
    fields=> url_id, url_title, url_link, url_description

    "1","Flickr","h ttp://flickr.com","Ph oto tool"
    "2","Google","h ttp://google.co.uk"," Search Engine"
    "3","Yahoo","ht tp://yahoo.co.uk","W eb Portal"

    lookup table=> fett_url_to_dat a
    fields=> url_id, data_id

    "2","1"
    "2","2"
    "2","6"
    "3","2"
    "3","5"
    "3","6"


    Positive SQL Query
    ------------------------------

    Query:

    SELECT *
    FROM fett_url_to_dat a
    LEFT JOIN fett_url
    ON fett_url_to_dat a.url_id = fett_url.url_id
    WHERE fett_url_to_dat a.data_id = 2
    ORDER BY url_title

    Returns:

    fields=> url_id, data_id, url_id, url_title, url_link, url_description

    "2","2","2","Go ogle","http://google.co.uk"," Search Engine"
    "3","2","3","Ya hoo","http://yahoo.co.uk","W eb Portal"


    Negative SQL Query
    ------------------------------

    Query:

    SELECT *
    FROM fett_url_to_dat a
    LEFT JOIN fett_url
    ON fett_url_to_dat a.url_id != fett_url.url_id
    WHERE fett_url_to_dat a.data_id = 2
    ORDER BY url_title

    Returns:

    fields=> url_id, data_id, url_id, url_title, url_link, url_description

    "2","2","1","Fl ickr","http://flickr.com","Ph oto tool"
    "3","2","1","Fl ickr","http://flickr.com","Ph oto tool"
    "3","2","2","Go ogle","http://google.co.uk"," Search Engine"
    "2","2","3","Ya hoo","http://yahoo.co.uk","W eb Portal"

    Tags: None
    • noone
      #2
      Re: Negative SQL Query

      abighill wrote:[color=blue]
      > Problem
      > -------------
      >
      > I want to return all URL records from 'fett_url' that are not currently
      > indexed in the lookup table 'fett_url_to_da ta' where 'data_id=2'.
      >
      > i.e.
      >
      > fields=> url_id, url_title, url_link, url_description
      >
      > "1","Flickr","h ttp://flickr.com","Ph oto tool"
      >
      > Previously I have managed to make a Positive SQL Query (see below)
      > when I try to reverse this including "!=" the results are not what I
      > require (See Negative SQL Query).
      >
      > Any ideas would be greatly appreciated.
      >
      >
      > Project Fett Data Stucture
      > -----------------------------------------
      >
      > table=> fett_url
      > fields=> url_id, url_title, url_link, url_description
      >
      > "1","Flickr","h ttp://flickr.com","Ph oto tool"
      > "2","Google","h ttp://google.co.uk"," Search Engine"
      > "3","Yahoo","ht tp://yahoo.co.uk","W eb Portal"
      >
      > lookup table=> fett_url_to_dat a
      > fields=> url_id, data_id
      >
      > "2","1"
      > "2","2"
      > "2","6"
      > "3","2"
      > "3","5"
      > "3","6"
      >
      >
      > Positive SQL Query
      > ------------------------------
      >
      > Query:
      >
      > SELECT *
      > FROM fett_url_to_dat a
      > LEFT JOIN fett_url
      > ON fett_url_to_dat a.url_id = fett_url.url_id
      > WHERE fett_url_to_dat a.data_id = 2
      > ORDER BY url_title
      >
      > Returns:
      >
      > fields=> url_id, data_id, url_id, url_title, url_link, url_description
      >
      > "2","2","2","Go ogle","http://google.co.uk"," Search Engine"
      > "3","2","3","Ya hoo","http://yahoo.co.uk","W eb Portal"
      >
      >
      > Negative SQL Query
      > ------------------------------
      >
      > Query:
      >
      > SELECT *
      > FROM fett_url_to_dat a
      > LEFT JOIN fett_url
      > ON fett_url_to_dat a.url_id != fett_url.url_id
      > WHERE fett_url_to_dat a.data_id = 2
      > ORDER BY url_title
      >
      > Returns:
      >
      > fields=> url_id, data_id, url_id, url_title, url_link, url_description
      >
      > "2","2","1","Fl ickr","http://flickr.com","Ph oto tool"
      > "3","2","1","Fl ickr","http://flickr.com","Ph oto tool"
      > "3","2","2","Go ogle","http://google.co.uk"," Search Engine"
      > "2","2","3","Ya hoo","http://yahoo.co.uk","W eb Portal"
      >[/color]


      SELECT a.url_id,
      a.url_title,
      a.url_link,
      a.url_descripti on,
      b.url_id,
      b.data_id
      FROM fett_url a
      LEFT OUTER JOIN fett_url_to_dat a b
      ON a.url_id = b.url_id
      WHERE a.data_id = 2
      and b.url_id is null
      ORDER BY a.url_title

      Because you are doing a left-outer join, if it does not exist in B
      (fet_url_to_dat a) it should return NULL.

      for consistency in all SQL-compliant databases, you will want to list
      each field, unless you are doing a single table select. - for
      simplicity, I always use an abbreviated table alias.

        Comment

        • abighill
          #3
          Re: Negative SQL Query

          Hi noone,

          I tried your suggested SQL query and phpMyAdmin returned:

          MySQL said:
          #1054 - Unknown column 'a.data_id' in 'where clause'

          This is because the 'data_id' does not exist in 'fett_url a' so I
          changed the SQL query to read:

          SELECT a.url_id,
          a.url_title,
          a.url_link,
          a.url_descripti on,
          b.url_id,
          b.data_id
          FROM fett_url a
          LEFT OUTER JOIN fett_url_to_dat a b
          ON a.url_id = b.url_id
          WHERE b.data_id = 2
          and b.url_id is null
          ORDER BY a.url_title

          This time the query ran successfully but nothing was returned.

            Comment

            • abighill
              #4
              Re: Negative SQL Query

              I posted the same probelm on the PHP Freaks Forum and Barand kindly
              solved the problem for me:

              SELECT u.*
              FROM fett_url u
              LEFT JOIN fett_url_to_dat a d
              ON d.url_id = u.url_id AND d.data_id = 2
              WHERE d.url_id IS NULL
              ORDER BY u.url_title

              Thanks everyone!

              See post here: http://www.phpfreaks.com/forums/inde...howtopic=87476

                Comment

                Previous template Next
                Working...