Confusing Select

[baileylo]

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.

[Dan Grossman]

What form does this list take? A piece of paper? A CSV file? A database table?

[baileylo]

it's a csv file.

[Dan Grossman]

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.

[r937]

1. upload the csv to a table
2. compare with your table using a LEFT OUTER JOIN

vwalah

[IBazz]

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

[r937]

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

[IBazz]

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