on 01-31-2014 11:09 AM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
have a look on the sample codes written in this thread:
How to model scenario in HANA | SCN
Regards,
Krishna Tangudu
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Can you please try this
SELECT * from A LEFT OUTER JOIN ( SELECT * from B left outer JOIN C on <condition> ) on <condition> ;
Regards,
Ashwini
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
84 | |
25 | |
12 | |
9 | |
6 | |
6 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.