# Along with join ,use some condition **URL:** \ **Category:** KNIME Analytics Platform\ **Created:** [October 15, 2019, 5:55am UTC](https://forum.knime.com/t/along-with-join-use-some-condition/18795 "2019-10-15T05:55:09Z")\ **Posts on this page:** 6\ **Page:** 1
**Author:** ![analytics\_sharma](https://forum-cdn.knime.com/letter_avatar_proxy/v4/letter/a/439d5e/32.png) [@analytics\_sharma](https://forum.knime.com/u/analytics_sharma)\ **Post date:** [October 15, 2019, 5:55am UTC](https://forum.knime.com/t/along-with-join-use-some-condition/18795/1 "2019-10-15T05:55:09Z")
Hi , How can we write if else condition along with with join query in Knime. For Eg-I have two tables Table A with columns name,city,addr1,addr2 Table B with columns name,city,addr1,addr2 read the column B.addr2 if below join gives some records select A.addr1,B.addr1 from tableA A left outer join tableB B on (A.name = B.name and A.city = B.city) else if above join gives null values, get any data from column A.addr2 before prefix-Ay,Bc,Dt. ---
**Author:** ![mlauber71](https://forum-cdn.knime.com/user_avatar/forum.knime.com/mlauber71/32/73590_2.png) [@mlauber71](https://forum.knime.com/u/mlauber71)\ **Post date:** [October 15, 2019, 9:44am UTC](https://forum.knime.com/t/along-with-join-use-some-condition/18795/2 "2019-10-15T09:44:50Z")
You could use a combination of CASE WHEN and SUBSTR() commands in SQL. For further manipulations, you should see what your SQL database would support. You might also try to generate it with generic KNIME DB nodes. Not sure if that would be sufficient. [kn\_example\_hive\_load\_files\_case\_when.knwf](https://forum-cdn.knime.com/uploads/default/original/2X/0/0834d34a736457741e33ce6af532c623e6b3a1c0.knwf) (38.4 KB) > DROP TABLE IF EXISTS default.table\_join\_c; > > CREATE TABLE default.table\_join\_c > AS SELECT > > CASE WHEN t1.Column1 IS NOT NULL > THEN t1.Column1 ELSE t2.Column1 END AS Column1 > , CASE WHEN t1.Column1 IS NOT NULL > THEN t1.Id ELSE t2.Id END AS Id > > , CASE WHEN t1.Column1 IS NOT NULL > THEN t1.aname > ELSE SUBSTR(t2.name\_new,1,3) END AS my\_new\_name > , t1.aname > > FROM default.table\_a t1 > > FULL OUTER JOIN > default.table\_b t2 > ON t1.aname = t2.name > AND t1.Id = t2.Id > ; ---
**Author:** ![analytics\_sharma](https://forum-cdn.knime.com/letter_avatar_proxy/v4/letter/a/439d5e/32.png) [@analytics\_sharma](https://forum.knime.com/u/analytics_sharma)\ **Post date:** [October 16, 2019, 4:35am UTC](https://forum.knime.com/t/along-with-join-use-some-condition/18795/3 "2019-10-16T04:35:20Z")
Thanks ,case when worked exactly what I was looking for:), but one thing substrr() will not work in , as I ma looking for prefix for specif strings, Do we have anything like split function or any regex function? ---
**Author:** ![ipazin](https://forum-cdn.knime.com/user_avatar/forum.knime.com/ipazin/32/806_2.png) [@ipazin](https://forum.knime.com/u/ipazin)\ **Post date:** [October 16, 2019, 2:59pm UTC](https://forum.knime.com/t/along-with-join-use-some-condition/18795/4 "2019-10-16T14:59:02Z")
Hi there @analytics_sharma, I have seen you opened another [topic](https://forum.knime.com/t/split-and-use-the-prefix-of-specific-strings/18853) for searching specific prefixes. Please link it in the future in order to keep out forum tidy and for users to easier find answers. EDIT: And this topic issue originated from [here](https://forum.knime.com/t/joining-data-from-two-tables-and-comparing-with-third-table/18147)… Additionally feel free to mark reply as solution for same above mentioned purposes. ![SolutionButton](https://forum-cdn.knime.com/uploads/default/original/2X/0/07432a5b8abe71d3e73d1eb1d7435335c08d6dc7.png) Br, Ivan ---
**Author:** ![mlauber71](https://forum-cdn.knime.com/user_avatar/forum.knime.com/mlauber71/32/73590_2.png) [@mlauber71](https://forum.knime.com/u/mlauber71)\ **Post date:** [October 16, 2019, 6:51pm UTC](https://forum.knime.com/t/along-with-join-use-some-condition/18795/5 "2019-10-16T18:51:36Z")
Hive does support regular expressions to a certain extent. Functions are replace and extract. > **[Hadoop Hive Regular Expression Functions and Examples - DWgeek.com](https://dwgeek.com/hadoop-hive-regular-expression-functions-examples.html/)** > > Hive regular expression functions, Syntax, Examples, hive Tutorials, REGEXP\_REPLACE, REGEXP\_EXTRACT, Hive regular Expression String Manipulation functions I am not sure about the implementation in the local big data environment though. To develop and test your regex you can use > **[Online regex tester and debugger: PHP, PCRE, Python, Golang and JavaScript](https://regex101.com/)** > > Regex101 allows you to create, debug, test and have your expressions explained for PHP, PCRE, Python, Golang and JavaScript. The website also features a community where you can share useful expressions. Or you might just ask @armingrudd for help 🙂 ---
**Author:** ![system](https://forum-cdn.knime.com/uploads/default/original/1X/ab3ccf34482a0329361734a18199390177204f15.png) [@system](https://forum.knime.com/u/system)\ **Post date:** [April 16, 2020, 6:56am UTC](https://forum.knime.com/t/along-with-join-use-some-condition/18795/6 "2020-04-16T06:56:34Z")
This topic was automatically closed 182 days after the last reply. New replies are no longer allowed.