include additional columns while using WHERE IN

Question

I have a table that looks like this.

id        name
1         firstName
2         secondName
3         thirdName
4         fourthName

I want to keep all rows where the name is present in either the "Country_name_EN" or "Country_Code" columns from the second table that looks like this:

Country_name_EN  Country_Code        coordinates
firstName       EN                  124
random          secondName          1244
thirdName       DE                  689
FifthName       DE                  457

I want a resulting table that looks like this. I also want to include the coordinates for all rows where the name is found in table2:

id        name                coordinates
1         firstName           124
2         secondName          1244
3         thirdName           689

This code works for the joins but not sure how to include the coordinates into it

select * 
  from `t1` as test
  where test.name in 
  (select test.name
      from `t2` as geonames 
      where geonames.Country_name_EN = test.name or geonames.Country_Code = test.name)

You posted the same question [yesterday](https://stackoverflow.com/questions/73753208/select-rows-whose-value-matches-with-either-of-the-two-columns-in-the-second-tab) and got the same answer: You need to do a left join to obtain also the coordinates column. You need to aggregate your results. You do not show that in your sample data. — Samuel, Sep 18 '22 at 08:55
Does this answer your question? [select rows whose value matches with either of the two columns in the second table:](https://stackoverflow.com/questions/73753208/select-rows-whose-value-matches-with-either-of-the-two-columns-in-the-second-tab) — Samuel, Sep 18 '22 at 08:57

nbk · Answer 1 · 2022-09-18T09:14:58.133

1

A WHERE IN clause can always be rewritten as join, which is usually fatser that IN in big databases, only with a JOIN you can access columns from both tables

So you do

SELECT `t1`.id, `t1`.name,`t2`.coordinates
from `t1` JOIN `t2` ON `t1`.name = `t2.Country_name_EN OR  geonames.Country_Code = `t1`.name

edited Sep 18 '22 at 09:14

answered Sep 17 '22 at 14:00

nbk

45,398
8
30
47

But this gives me 26k rows while the original t1 table only had 1k. The resulting dataset should be shorter @H – x89 Sep 17 '22 at 14:02
then you have in the t2 table multiple names that corresponds to t1, try it in a fiddle the code works with every database and see. Joining is the only way to get the additional column – nbk Sep 17 '22 at 14:05
ah is there any way we can stop checking after one match has been found? For example, if the country_name_en matches, no need to check and match with the next? @ – x89 Sep 17 '22 at 14:07
You can add a ROW_NUMBER OVER PARTITION BY name) and use an outer sql to limit the number of rows, but as always with limiting the number you need an ORDER BY – nbk Sep 17 '22 at 14:09
@nbk your answer ist good, but see same discussion as here : https://stackoverflow.com/questions/73753208/select-rows-whose-value-matches-with-either-of-the-two-columns-in-the-second-tab – Samuel Sep 18 '22 at 08:59

include additional columns while using WHERE IN

1 Answers1