join two columns with different column name(need help)

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

Code:

---

  emp_refno              emp_permitno 
  12                        10000000000
  15                        50000000000
  22                        40000000000

---

employee

Code:

---

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

Code:

---

  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

Code:

---

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

---

and here is my code...

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.

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

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
  and emp.emp_jobno=act.act_jobno
  where ref.emp_permitno = '10000000000'

---

Quote:

---

Originally Posted by gk53

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

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
  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

sql i posted return on my server

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

another option you can try

Code:

---

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'

---

Quote:

---

Originally Posted by gk53

another option you can try

Code:

---

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.

Quote:

---

Originally Posted by jemz

but my query did not work

---

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

Quote:

---

Originally Posted by jemz

... and the records are repeating.

---

that's impossible

it uses DISTINCT, so every result row is unique

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

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....

Quote:

---

Originally Posted by r937

that's impossible

it uses DISTINCT, so every result row is unique

---

Hi r937, yes it display repeatedly...

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

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...

Quote:

---

Originally Posted by 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.

Quote:

---

Originally Posted by 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...

---

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:

Code:

---

mytable1

empno        age
0001          25
0002          30
0003          45


mytable2

projectno
1000
2000
3000

---

then the output will be like this

Code:

---

mixcolumn
0001         
0002         
0003
1000
2000
3000

---

thank you in advance.