Matching values without to rewrite a very extensive query more than once SAP HANA

Question

I'm racking my brain over this little problem.

I have a very large query B which generates a Table B.

A query A that generates a list of customers Table A.

Both tables share "FACILITY" but the Neighborhoods values are not necessarily the same.

I need to join table B to Table A but I need to create a CONDITION column, Value 1 if NEIGHBORHOOD_A is found in NEIGHBORHOOD_B (even if FACILITY is not equal in Table B).

My dilemma is that Query B is very large and the way I can think of to generate a list is to rewrite query B, I think there must be some function that allows me to do this easily in a select statement like this:

SELECT
    CUSTOMER,
    A.FACILITY,
    B.FACILITY,
    NEIGHBORHOOD_A,
    NEIGHBORHOOD_B
    --Function that allows me to put a 1 if NEIGHBORHOOD_A is within any of the values of NEIGHBORHOOD_B
FROM
    (query_A) A
LEFT JOIN 
    (query_B) B ON A.FACILITY = B.FACILITY

Table A

CUSTOMER	FACILTY	NEIGHBORHOOD_A
1	A	N1
2	B	N2
3	C	N3
4	D	N1
5	E	N4
6	F	N5

Table B (The sub Query that generates this table is very long and I don't want to have to rewrite it to generate a list of neighborhoods)

FACILITY	NEIGHBORHOOD_B
A	N3
B	N2
C	N3
F	N1
Z	N4

Desired result

Customer	FACILITY	NEIGHBORHOOD_A	NEIGHBORHOOD_B	CONDITION
1	A	N1	N3	1
2	B	N2	N2	1
3	C	N3	N3	1
4	D	N1	NULL	1
5	E	N4	NULL	1
6	F	N5	N1	0

Answer 1

If I understood you correctly, the solution is probably not as sophisticated as you may think.

Data Sample

CREATE TABLE table_a (customer integer, facility varchar(1), neighborhood_a varchar(2));
INSERT INTO table_a VALUES (1, 'A', 'N1');
INSERT INTO table_a VALUES (2, 'B', 'N2');
INSERT INTO table_a VALUES (3, 'C', 'N3');
INSERT INTO table_a VALUES (4, 'D', 'N1');
INSERT INTO table_a VALUES (5, 'E', 'N4');
INSERT INTO table_a VALUES (6, 'F', 'N5');


CREATE TABLE table_b (facility varchar(1), neighborhood_b varchar(2));
INSERT INTO table_b VALUES ('A', 'N3');
INSERT INTO table_b VALUES ('B', 'N2');
INSERT INTO table_b VALUES ('C', 'N3');
INSERT INTO table_b VALUES ('F', 'N1');
INSERT INTO table_b VALUES ('Z', 'N4');

Query with sub-select in projection

SELECT *,
    CASE WHEN neighborhood_a IN (SELECT DISTINCT neighborhood_b FROM table_b) THEN 1 ELSE 0 END AS cond
FROM table_a a
LEFT JOIN table_b b ON a.facility = b.facility 
ORDER BY a.customer, a.facility

You can also solve this via join

WITH neighborhoods AS
(
    SELECT DISTINCT neighborhood_b FROM table_b
)
SELECT *,
    CASE WHEN n.neighborhood_b IS NULL THEN 0 ELSE 1 END AS cond
FROM table_a a
LEFT JOIN table_b b ON a.facility = b.facility 
LEFT JOIN neighborhoods n ON a.neighborhood_a = n.neighborhood_b
ORDER BY a.customer, a.facility

In any case, please note that your condition field is independent from the original join with table_b, that you have proposed. So, if you don't need the information, that has been joined by facility and if it was just part of your anticipated solution, you may also remove this part to simplify the statement.

Simplified version with projection

SELECT *, CASE WHEN neighborhood_a IN (SELECT DISTINCT neighborhood_b FROM table_b) THEN 1 ELSE 0 END AS cond
FROM table_a a
ORDER BY a.customer, a.facility

Simplified version with join

SELECT a.*, CASE WHEN b.neighborhood_b IS NULL THEN 0 ELSE 1 END AS cond
FROM table_a a
LEFT JOIN (SELECT DISTINCT neighborhood_b FROM table_b) b 
    ON a.neighborhood_a = b.neighborhood_b
ORDER BY a.customer, a.facility

Result

Of course, you will have to replace table_a and table_b with your respective sub-selects. Also, since I do not know the sub-selects, I am not able to evaluate if these statements make sense from a performance perspective. Functionally, it should do the job.