2

I have two SPARK SQL Tables as follows,

Table 1

email           |  client_ip    |  travelling_method | travelling_code
person1@abc.com |203.22.22.22   | Car                | car001
person1@abc.com |203.22.22.22   | Jeep               | jeep001

Table 2 

email           |  client_ip    |  account_type | trav_code
person1@abc.com |203.22.22.22   | true          | car
person1@abc.com |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 = 'person1@abc.com';

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
person1@abc.com |203.22.22.22   | Car                | car001         | true
person1@abc.com |203.22.22.22   | Jeep               | jeep001        | false

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

Praveena Gunasekera
  • 79
  • 1
  • 8

1 Answers1

0

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 = 'person1@abc.com'

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

Jhon
  • 137
  • 1
  • 4
  • 14