SitePoint Sponsor

User Tag List

Results 1 to 15 of 15
  1. #1
    SitePoint Wizard
    Join Date
    May 2012
    Posts
    1,107
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

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

  2. #2
    SitePoint Enthusiast
    Join Date
    Aug 2011
    Location
    OH, USA
    Posts
    72
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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'

  3. #3
    SitePoint Wizard
    Join Date
    May 2012
    Posts
    1,107
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by gk53 View Post
    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

  4. #4
    SitePoint Enthusiast
    Join Date
    Aug 2011
    Location
    OH, USA
    Posts
    72
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    sql i posted return on my server

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

  5. #5
    SitePoint Enthusiast
    Join Date
    Aug 2011
    Location
    OH, USA
    Posts
    72
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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'

  6. #6
    SitePoint Wizard
    Join Date
    May 2012
    Posts
    1,107
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by gk53 View Post
    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.

  7. #7
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,501
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by jemz View Post
    but my query did not work
    Could you give some more information?
    What is the output your query gives you?

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,276
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by jemz View Post
    ... and the records are repeating.
    that's impossible

    it uses DISTINCT, so every result row is unique
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Enthusiast
    Join Date
    Aug 2011
    Location
    OH, USA
    Posts
    72
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    how you expect have 4 different jobOBNO
    10000
    20000
    10010
    20020
    if you have only 2 act_jobno in table 10010 and 20020?

  10. #10
    SitePoint Enthusiast
    Join Date
    Aug 2011
    Location
    OH, USA
    Posts
    72
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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....

  11. #11
    SitePoint Wizard
    Join Date
    May 2012
    Posts
    1,107
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    that's impossible

    it uses DISTINCT, so every result row is unique
    Hi r937, yes it display repeatedly...

  12. #12
    SitePoint Wizard
    Join Date
    May 2012
    Posts
    1,107
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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

  13. #13
    SitePoint Enthusiast
    Join Date
    Aug 2011
    Location
    OH, USA
    Posts
    72
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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...

  14. #14
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,501
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by guido2004 View Post
    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.

  15. #15
    SitePoint Wizard
    Join Date
    May 2012
    Posts
    1,107
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by gk53 View Post
    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.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •