cancel
Showing results for 
Search instead for 
Did you mean: 

Left Outer Join Query for AMDP

Former Member
0 Kudos

Hello.

I have 3 tables.

Table A ---> Left outer Join to Table B..

Table B ---> Left Outer join to Table C.

How to write a Query for these kinds of tables in HANA.

I wrote like

select * from A left outer join B on (condition)

                       left outer join C on condition.

Please propose some way to write this.

Best regards,

Rohit

Accepted Solutions (0)

Answers (5)

Answers (5)

former_member184768
Active Contributor
0 Kudos

Hi Rohit,

It will help if you provide more details on your table structure and expected output structure. Basically what you wrote is not wrong, just that it needs some clarification like


Rohit Mahajan wrote:

Hello.

I have 3 tables.

Table A ---> Left outer Join to Table B..

Table B ---> Left Outer join to Table C.

How to write a Query for these kinds of tables in HANA.

I wrote like

select * from A left outer join B on (condition : A.join_col_1 = B.join_col_1 and A.join_col_2 = B.join_col_2)

                       left outer join C on (condition : A.join_col_11 = C.join_col_11 and A.join_col_22 = C.join_col_22)

Please propose some way to write this.

Best regards,

Rohit

the join condition depends upon how you wish to join the tables based on the column values.

Ravi

Former Member
0 Kudos

Hi,

Let's say, we have 3 tables with following columns:

TABLE A: SNWD_SO (SalesOrder table) - SO_ID (sales order id),BP_ID (business partner id),BUYER_GUID etc.

TABLE B:SNWD_BPA (Business Partner )- NODE_KEY,COMPANY_NAME,BP_ID

FIRST_NAME,LAST_NAME,PHONE_NUMBER,ADDRESS_GUID etc.

TABLE C: SNWD_BPA_CONTACT - ADDRESS_GUID, PHONE_NUMBER,

FIRST_NAME,LAST_NAME etc.

Using left outer join, we want to have the sales order information(SO_ID) and customer information(BP_ID,COMPANY_NAME,PHONE_NUMBER,FIRST_NAME,LAST_NAME)

The query will be:

select A.SO_ID as SalesOrderID ,sub1.BP_ID as BPID,sub1.COMPANY_NAME as CompanyName,sub1.FIRST_NAME as FirstName,sub1.LAST_NAME as LastName,

sub1.PHONE_NUMBER as PhoneNumber from SNWD_SO as A left outer join

(select b.NODE_KEY,b.COMPANY_NAME,b.BP_ID, c.PHONE_NUMBER,c.FIRST_NAME,c.LAST_NAME from  snwd_bpa_contact as c left outer join snwd_bpa as b

on  c.ADDRESS_GUID=b.ADDRESS_GUID ) as sub1

on A.BUYER_GUID=sub1.NODE_KEY

order by sub1.COMPANY_NAME asc ;

Table B and C are joined using ADDRESS_GUID column in both tables and the result is in SUB1.

Table A and SUB1 are joined using BUYER_GUID from A and NODE_KEY from SUB1.

Regards,

Amita


former_member182302
Active Contributor
0 Kudos

have a look on the sample codes written in this thread:

How to model scenario in HANA | SCN

Regards,

Krishna Tangudu

0 Kudos

Can you please try this

SELECT * from A LEFT OUTER JOIN ( SELECT * from B left outer JOIN C on <condition> ) on <condition> ;

Regards,

Ashwini

former_member182302
Active Contributor
0 Kudos

Hi Rohit,

Written something similar ( not entirely) may be this can help you to get thoughts on how to write.

Regards,

Krishna Tangudu

Former Member
0 Kudos

Hi,

Left Outer inside Left outer as you mentioned is not giving proper results.

Rohit