Using LIKE to compare two columns in SPARK SQL

Question

I have two SPARK SQL Tables as follows,

Table 1

email           |  client_ip    |  travelling_method | travelling_code
[email protected] |203.22.22.22   | Car                | car001
[email protected] |203.22.22.22   | Jeep               | jeep001              

Table 2

email           |  client_ip    |  account_type | trav_code
[email protected] |203.22.22.22   | true          | car
[email protected] |203.22.22.22   | false         | jeep  

My query is as follows,

SELECT table1.email, table1.client_ip, table1.travelling_method, table1.travelling_code, table2.account_type FROM table1 LEFT JOIN table2 ON table1.email = table2.email AND table1.client_ip = table2.client_ip AND table1.travelling_code LIKE CONCAT('%' ,table2.trav_code, '%') WHERE table1.email = '[email protected]';

I wrote the above query to get the following output but it throws me with an Exception.

The desired output is:

email           |  client_ip    |  travelling_method | travelling_code| account_type
[email protected] |203.22.22.22   | Car                | car001         | true
[email protected] |203.22.22.22   | Jeep               | jeep001        | false

Appreciate if someone could help me out to point me out what im missing in my query. :)

Answer 1

Try writing " where " in right place like

SELECT table1.email, table1.client_ip, table1.travelling_method, table1.travelling_code, table2.account_type 
 FROM table1 LEFT JOIN table2 ON table1.email = table2.email WHERE 
 table1.client_ip = table2.client_ip 
 AND table1.travelling_code LIKE CONCAT('%' ,table2.trav_code, '%') and table1.email = '[email protected]'

I did not test your query. If still face error then post with error message.