question

Hercules_TS avatar image
Hercules_TS asked

SQL select syntax to compare list of values against sql table

i have an excel with 3 columns. column1 lastname, column2 firstname, column3 fathername. I exported these data from excel and make a list of data like this... (Marley,Bob,George); (Jackson, Michael, Paul); (Connor, Sarah,Cathrine) etc. In SQL (2008 r2), i have a table that has these 3 columns (and much more..). I want to search if my table have persons exported from excel I found a solution with repeatedly selects and union all at the end, but the rows i have is 5000 exported of excel against 70000 in sql table so i think that is not the best way with repeatedly unions. So my question is if there is a syntax that is no need to repeat again and again the select, and instead of this use something like select lastname,firstname,fathername from table where lastname,firstname,fathername in (Marley,Bob,George); (Jackson, Michael, Paul); (Connor, Sarah,Cathrine) The results i want to return is lastname, firtname,fathername from people that is same in select list and sql table Thanks a lot for youur time, Hercules
sqlsql-server-2008-r2select
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
ThomasRushton avatar image
ThomasRushton answered
Sarah Connor's father is called Cathrine? Seriously, though - what you need is to look into [JOINs][1]. In your case, something like this should do the job: SELECT * FROM exporteddata INNER JOIN MyTable ON exporteddata.firstname = mytable.firstname AND exporteddata.surname=mytable.surname AND exportteddata.fathername=mytable.fathername This will return all rows from both tables where those three values / fields match. [1]: https://technet.microsoft.com/en-us/library/ms191517(v=sql.105).aspx
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton avatar image ThomasRushton ♦♦ commented ·
Not necessarily - you might be able to use something like an OPENROWSET query to your Excel sheet to treat it as a table in its own right. See https://msdn.microsoft.com/en-us/library/ms190312.aspx
1 Like 1 ·
Hercules_TS avatar image Hercules_TS commented ·
hahaha...you are absolutely right!!! i messed up the example. Seriously now, you mean that i need to create table and fill it with exported data and then join the 2 tables and compare them, or something else? Thanks again for your time
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.