select rows whose value matches with either of the two columns in the second table:-CodePudding

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 "testName" or "prodName" columns from the second table that looks like this:

testName        prodName            coordinates
firstName       EN                  124
random          secondName          1244
thirdName       DE                  689
FifthName       DE                  457

I want a resulting table that looks like this:

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

Something like this worked if i was comparing with only one other column from table2:

(select * 
  from `table1`
  where exists 
    (select 1
      from `table2` where testName = `table1`.name ));

but this would throw an error:

(select * 
  from `table1`
  where exists 
    (select 1
      from `table2` where testName = `table1`.name OR prodName = `table1`.name ));

LEFT SEMI JOIN cannot be used without a condition that is an equality of fields from both sides of the join.

CodePudding user response：

You need a left join to include the field coordinate. Please see my comment for that.

Here the correct Where filter:

With table1 as 
(Select row_number() over() as id, * from unnest(["firstName","secondName","thirdName","fourthName"]) name
),table2 as 
(select "firstName" as testname, "NB" as prodName
union all select "BG","secondName") 

select * 
  from `table1`
  where exists 
    (select 1 from `table2` where testName = `table1`.name
    union all Select 1 from table2 where prodName = `table1`.name )

CodePudding user response：

Please try this:

SELECT 
A.id
,A.name
,B.coordinates

FROM table1 A
INNER JOIN table2 B
ON A.name=B.testName OR A.name=B.prodName