SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Addict
    Join Date
    Jul 2007
    Location
    San Jose, California
    Posts
    355
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Confusing Select

    I have a list of names and I want to see what names are on the list and not in the database table.

    I was originally thinking

    select * from tbl1 where name is not in(list)

    but that is the wrong way and will display in the db but not in the db.

  2. #2
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    What form does this list take? A piece of paper? A CSV file? A database table?

  3. #3
    SitePoint Addict
    Join Date
    Jul 2007
    Location
    San Jose, California
    Posts
    355
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    it's a csv file.

  4. #4
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Select all the names from the database table. Loop over your CSV file in your favorite programming language and print/save the names that are not also in what you got from the DB. Depending on what language you use there may be functions to find the intersection of two arrays and subtract that intersection to remove the names in both lists.

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    1. upload the csv to a table
    2. compare with your table using a LEFT OUTER JOIN

    vwalah

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

  6. #6
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This is a big guess but, when you have identified those values, which are not in the db, are you planning on inserting each of them to the db table?

    If so, look up insert and the different variations. I am thinking of INSERT IGNORE, since I found it very useful yesterday and it shortened the code considerably. There are others too.


    bazz

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    bazz, i ~really~ like how you are so willing to share what you've learned

    what you've anticipated here is very likely the real reason for the original question

    nice job

    note that INSERT IGNORE is not standard SQL, but can be extremely useful for various situations

    in cases where there is a difference betwee standard SQL and proprietary SQL, here's my advice:

    - if the database system supports the standard SQL syntax, always use that

    - if the database system has a feature that is not covered by standard SQL syntax, and that feature is useful, go ahead and use it

    without the IGNORE option, the problem is a lot trickier

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

  8. #8
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    bazz, i ~really~ like how you are so willing to share what you've learned
    Thanks very much.
    I guess I am open source personified then (joke) coz I don't believe I can have a proprietary right to knowledge when I received it from someone else.

    without the IGNORE option, the problem is a lot trickier
    tell me about it!! I have a bald patch caused by that 'trickiness'

    Bazz


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
  •