SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Addict thoresson's Avatar
    Join Date
    Dec 2002
    Location
    Gothenburg, Sweden
    Posts
    255
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    choosing records where value is not present

    Hi,

    I'm having two tables, un_article and un_writer. I want to get all records from un_article except those who have a value in column un_article.a_id that is also present in un_writer.a_id. How do I do this?

    Best regards,

    Anders

  2. #2
    SitePoint Zealot tcwatts's Avatar
    Join Date
    Jan 2003
    Location
    .my
    Posts
    131
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    How about this?

    SELECT un_article.* FROM un_article, un_writer WHERE un_article.a_id != un_writer.a_id

    HTH

  3. #3
    SitePoint Wizard
    Join Date
    Nov 2000
    Location
    Chico, Ca
    Posts
    1,125
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by tcwatts
    How about this?

    SELECT un_article.* FROM un_article, un_writer WHERE un_article.a_id != un_writer.a_id

    HTH
    Gotta love SQL !
    "Happiness doesn't find you, you find happiness" -- Unknown
    www.chuckknows.com

  4. #4
    SitePoint Addict thoresson's Avatar
    Join Date
    Dec 2002
    Location
    Gothenburg, Sweden
    Posts
    255
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by tcwatts
    SELECT un_article.* FROM un_article, un_writer WHERE un_article.a_id != un_writer.a_id
    Nope. That didn't work. With query = "SELECT un_article.a_id FROM un_article, un_writer WHERE un_article.a_id != un_article.a_id"; I get

    +------+
    | a_id |
    +------+
    | 1 |
    | 2 |
    | 3 |
    | 6 |
    | 1 |
    | 2 |
    | 3 |
    +------+

    when what I expect to get is

    +------+
    | a_id |
    +------+
    | 1 |
    | 2 |
    | 3 |
    +------+

    Since a_id = 6 exists in un_writer.

  5. #5
    SitePoint Zealot tcwatts's Avatar
    Join Date
    Jan 2003
    Location
    .my
    Posts
    131
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thoresson,

    you have an error in your statement -

    "SELECT un_article.a_id FROM un_article, un_writer WHERE un_article.a_id != un_article.a_id";

    The last bit should be "WHERE un_article.a_id != un_writer.a_id"; you could add GROUP BY a_id if you do not want repeats.


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
  •