join two columns with different column name(need help)

[jemz]

Hi, I need some help on my tables...i want to display the emp_jobno and the act_jobno as one column.


This is the structure of my tables


empreference

   emp_refno              emp_permitno   
   12                         10000000000
   15                         50000000000
   22                         40000000000

employee

 emp_id            emp_name                        emp_address        emp_jobno          emp_refno    emp_date     
 0001               mathias                            sweeden             10000                 12              2011-10-10
 0002               Beth                                england               20000                 12              2011-10-12

activity

  actno            act_jobno            act_status              act_date                  emp_permitno
  00001           10010                  A                          2013-5-12                10000000000
  00002           20020                  A                          2013-2-06                10000000000

The expected output must be like this

ACTIVITYDATE              JOBNO               
2011-10-10                    10000                                 
2011-10-12                    20000               
2013-5-12                     10010                               
2013-2-06                     20020

and here is my code...

   select act.act_date,emp.emp_jobno,emp.emp_name
  
   from employee emp inner join empreference ref
   on  emp.emp_refno = ref.emp_refno
   inner join activity act
   on act.emp_permitno = ref.emp_permitno
   where ref.emp_permitno = '10000000000'

but my query did not work...please help me on this.thank you in advance.

[gk53]

you ned to add one more join condition and emp.emp_jobno=act.act_jobno

select  act.act_date,emp.emp_jobno,emp.emp_name
   from employee emp inner join empreference ref
   on  emp.emp_refno = ref.emp_refno
   inner join activity act
   on act.emp_permitno = ref.emp_permitno
   and emp.emp_jobno=act.act_jobno
   where ref.emp_permitno = '10000000000'

[jemz]

you ned to add one more join condition and emp.emp_jobno=act.act_jobno

select  act.act_date,emp.emp_jobno,emp.emp_name
   from employee emp inner join empreference ref
   on  emp.emp_refno = ref.emp_refno
   inner join activity act
   on act.emp_permitno = ref.emp_permitno
   and emp.emp_jobno=act.act_jobno
   where ref.emp_permitno = '10000000000'

Hi gk53, I already add another join but it stops,it did not display

[gk53]

sql i posted return on my server

act_date emp_jobno emp_name
2013-05-12 10000 mathias
2013-05-12 20000 Beth

[gk53]

another option you can try

select distinct act.act_date,emp.emp_jobno,emp.emp_name
     from employee emp inner join empreference ref
   on  emp.emp_refno = ref.emp_refno
   inner join activity act
   on act.emp_permitno = ref.emp_permitno
   where ref.emp_permitno = '10000000000'

[jemz]

another option you can try

select distinct act.act_date,emp.emp_jobno,emp.emp_name
     from employee emp inner join empreference ref
   on  emp.emp_refno = ref.emp_refno
   inner join activity act
   on act.emp_permitno = ref.emp_permitno
   where ref.emp_permitno = '10000000000'

it gives me wrong output and the records are repeating.

[guido2004]

but my query did not work

Could you give some more information?
What is the output your query gives you?

[r937]

... and the records are repeating.

that's impossible

it uses DISTINCT, so every result row is unique

[gk53]

how you expect have 4 different jobOBNO
10000
20000
10010
20020
if you have only 2 act_jobno in table 10010 and 20020?

[gk53]

in your expected output you have first 2 rows emp_date and emp_jobno from employee table
and rows 3 and 4 from table activity....

[jemz]

that's impossible

it uses DISTINCT, so every result row is unique

Hi r937, yes it display repeatedly...

[jemz]

hi gk53, Yes that's what i want for my expected output...but i tried your distinct i get in trouble...i don't know why

[gk53]

but as said in previous post

ACTIVITYDATE JOBNO
2011-10-10 10000 - from employee table
2011-10-12 20000 - from employee table
2013-5-12 10010 - from activity table
2013-2-06 20020 - from activity table

it is not right...

[guido2004]

Could you give some more information?
What is the output your query gives you?

Let me repeat myself.
Saying "the query doesn't work", "It gives me wrong output" is not helpful at all since we can't see what you see.

You told us what outcome you want. Now please tell us what outcome you are getting right now, with the query you posted in your first post in this thread. Or with the query as you have it in this moment.

[jemz]

but as said in previous post

ACTIVITYDATE JOBNO
2011-10-10 10000 - from employee table
2011-10-12 20000 - from employee table
2013-5-12 10010 - from activity table
2013-2-06 20020 - from activity table

it is not right...

Hi, you mean that it is not right my outcome example...so what should i do so that i can get the column from other table and another column from other table

and join them to create a resulting set.

example:

mytable1

empno         age
0001           25
0002           30
0003           45


mytable2

projectno
1000
2000
3000

then the output will be like this

mixcolumn
0001          
0002           
0003 
1000
2000
3000

thank you in advance.