jemz
January 17, 2013, 5:04pm
1
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
January 17, 2013, 5:25pm
2
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
January 17, 2013, 5:32pm
3
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'
Hi gk53, I already add another join but it stops,it did not display
gk53
January 17, 2013, 5:55pm
4
sql i posted return on my server
act_date emp_jobno emp_name
2013-05-12 10000 mathias
2013-05-12 20000 Beth
gk53
January 17, 2013, 5:56pm
5
another option you can try
select [COLOR="#FF0000"]distinct[/COLOR] 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
January 17, 2013, 6:01pm
6
gk53:
another option you can try
select [COLOR="#FF0000"]distinct[/COLOR] 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.
Could you give some more information?
What is the output your query gives you?
r937
January 17, 2013, 6:29pm
8
that’s impossible
it uses DISTINCT, so every result row is unique
gk53
January 17, 2013, 6:32pm
9
how you expect have 4 different jobOBNO
10000
20000
10010
20020
if you have only 2 act_jobno in table 10010 and 20020?
gk53
January 17, 2013, 6:36pm
10
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
January 17, 2013, 7:17pm
11
Hi r937, yes it display repeatedly…
jemz
January 17, 2013, 7:19pm
12
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
January 17, 2013, 8:15pm
13
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…
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
January 19, 2013, 5:25pm
15
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:
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.