topic Re: selecting from two table in Application Development and Automation Discussions

selecting from two table

Former Member — Thu, 21 Feb 2013 16:13:49 GMT

Hi all,

I'm now work in a table that compine data from tow table .

table display employee Id, employee name, a_time, l_time, and delay.

ztable1 contain employee Id, date, a_time, l_time, and delay.

ztable2 contain employee Id, employee name.

*select
*
* A~EMPLOYEE_ID
* B~EMP_NAME
* A~A_TIME
* A~L_TIME
* A~DELAY into CORRESPONDING FIELDS OF TABLE internaltable

*FROM ztable1 as A INNER JOIN ztable2 as B on A~EMPLOYEE_ID = B~EMP_ID and A~DATE1 = spisified_date.

No result found, and there is no errors in the code.Could any one help???? I think there is error in the select statment.

thanks for all.

Re: selecting from two table

former_member361112 — Thu, 21 Feb 2013 16:18:58 GMT

Hello Huda

please insert in the code the WHERE sentence after the ON sentence,

Best regards

Re: selecting from two table

Former Member — Thu, 21 Feb 2013 16:28:25 GMT

*Select A~EMPLOYEE_ID

* B~EMP_NAME

* A~A_TIME

* A~L_TIME

* A~DELAY into TABLE lt_attendance_node

*from ztable1 as A

*inner join ztable2 as B on EMPLOYEE_ID = B~EMP_ID where A~DATE1 = spisified_date.

still didn't work ,, Iwant to select all table1 ,and the name of employee from another table

Re: selecting from two table

Former Member — Thu, 21 Feb 2013 17:22:12 GMT

Hi Huda,

The select query looks fine. Since it's a ZTable, please check if the data exists in corresponding tables for your search criteria given in the query.

That might be an issue.

Thanks

Vivek

Re: selecting from two table

Former Member — Thu, 21 Feb 2013 17:45:25 GMT

Hi Vivek Batta,

thank you for your reply.

I try to do the search in table1 where the date = spisified_date.

and it works all what I want from table2 is to disply the employee name. But the select which contain table2 didn't work. employee ID in both tables.

could you help please???

Re: selecting from two table

Former Member — Thu, 21 Feb 2013 18:00:06 GMT

Hi Huda,

in your 1st post, u mentioned the structure of your Z tables as below :

ztable1 contain employee Id, date, a_time, l_time, and delay.

ztable2 contain employee Id, employee name.

but in your query , it's written, B~EMP_ID ??

select
*
* A~EMPLOYEE_ID
* B~EMP_NAME
* A~A_TIME
* A~L_TIME
* A~DELAY into CORRESPONDING FIELDS OF TABLE internaltable

*FROM ztable1 as A INNER JOIN ztable2 as B on A~EMPLOYEE_ID = B~EMPLOYEE_ID and A~DATE1 = spisified_date.

This should work

Thanks

Vivek

Re: selecting from two table

Former Member — Thu, 21 Feb 2013 18:15:59 GMT

hello Huda,

table1 and table 2 both has a field employee_id....check both domain and data type and no. of characters in both tables..... any foreign key relationship has already been created between those table???

Thanks

Sabyasachi

Re: selecting from two table

RaymondGiuseppi — Thu, 21 Feb 2013 18:19:22 GMT

Try

SELECT A~EMPLOYEE_ID B~EMP_NAME A~A_TIME A~L_TIME A~DELAY 
  INTO itab as A
  INNER JOIN ztable2 as B 
    on  A~EMPLOYEE_ID = B~EMP_ID 
  WHERE 
     A~DATE1 = spisified_date.

Regards,

Raymond

Re: selecting from two table

Former Member — Thu, 21 Feb 2013 18:23:10 GMT

Hello Huda,

i think you have to create a foreign key relationship between both tables based on employee id......

hope your problem will be solved...

Thanks

Sabyasachi

Re: selecting from two table

Former Member — Thu, 21 Feb 2013 18:24:50 GMT

ztable1:

CLIENT type MANDT

ATT_ID type Z_ID

EMPLOYEE_ID typeZEMPLOYEE_ID

DATE1 type ZDATE

A_TIME type ZATIME

L_TIME type ZLTIME

PERM_ type TIME ZPERM_TIME

DELAY type ZDELAY

ztable2:

CLIENT type MANDT

EMP_ID type Z_ID

EMP_NAME type Z_NAME

TIM_ID type Z_ID

PASSWORD type ZPASS

Appreciate your cooperation

Re: selecting from two table

Former Member — Thu, 21 Feb 2013 18:25:48 GMT

I thenk this is will help ,,I will make forign key

Re: selecting from two table

Former Member — Thu, 21 Feb 2013 18:32:52 GMT

Hello,

in Ztable1 :

EMPLOYEE_ID typeZEMPLOYEE_ID

in your ztable2

EMP_ID type Z_ID.

check that both domain are same or not??.....to create a foreign key relationship both domain must be same.. create a foreign key relationship......then execute your code......

Hope it will be helpfull.......

Thanks

Sabyasachi

Re: selecting from two table

Former Member — Thu, 21 Feb 2013 19:09:39 GMT

It's work

I change the data type in the table after deleting all data ......and it work now .....thank you very much for your help

Re: selecting from two table

Former Member — Fri, 22 Feb 2013 04:19:03 GMT

Hi Huda..

whenever you are writing a select statement to get the data from database table into the internal table

dont use into corresponding fields of table i-tab...it is a big performance issue..instead you use the same structure what you had declared ...