Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2011
    Posts
    4

    Post Unanswered: DB experts Plz help me

    i have th following three tables

    table1
    projcode
    projname

    table2

    empcode
    empname
    rmcode

    table3

    projcode
    empcode
    testknowledge
    process




    i have to retrieve the data in columns as follows on supplying the project code(projcode):


    empcode empname rmcode rmname testknowledge process



    to note

    1.rmcode is reporting manager code this is any one of the value from empcode
    rmname is reporting manager name this is any one of the value from empname,
    bcoz reporting manager is also an employee.

    2.i dont have the rmname as a column in the table2 since the rmname is available in table2 as empname


    can someone help me how to get the columns i dont know how to link the rmcode and rmname.

    Code:
    select empcode,(select empname from table2 where table3.empcode=table2.empcode)"Name",(i dont know how to get the rmcode),(i dont know how to get the rmname),(select process from table2)"process" from table3 where projcode='1001'

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Is this a test for a SQL Developer position with IBM? So, if we give you an answer, you'll get the job? Nice!!!

    select
    t2.empcode
    ,t2.empname
    ,t2.rmcode
    ,rmname = t2.empname
    ,t3.testknowledge
    ,t3.process
    from table2 t2
    inner join table3 t3 on t2.empcode = t3.empcode
    where exists (select * from table1 t1 where t3.projcode = t1.projcode)

    ...I think...But I know for sure Rudy will get it right, and maybe even give you a couple of versions of it.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT table2.empcode 
         , table2.empname 
         , table2.rmcode
         , mgr.empname AS rmname 
         , table3.testknowledge 
         , table3.process
      FROM table3
    INNER
      JOIN table2
        ON table2.empcode = table3.empcode
    LEFT OUTER
      JOIN table2 AS mgr
        ON mgr.empcode = table2.rmcode
     WHERE table3.projcode = '1001'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jan 2011
    Posts
    4

    thank you

    Quote Originally Posted by rdjabarov View Post
    Is this a test for a SQL Developer position with IBM? So, if we give you an answer, you'll get the job? Nice!!!

    select
    t2.empcode
    ,t2.empname
    ,t2.rmcode
    ,rmname = t2.empname
    ,t3.testknowledge
    ,t3.process
    from table2 t2
    inner join table3 t3 on t2.empcode = t3.empcode
    where exists (select * from table1 t1 where t3.projcode = t1.projcode)

    ...I think...But I know for sure Rudy will get it right, and maybe even give you a couple of versions of it.

    thank you
    and this was just a part of my project assignment
    my team leader had asked me not to use any joins in the query
    that was my problem.

    can u tell me a query without joins ???plzz

  5. #5
    Join Date
    Jan 2011
    Posts
    4

    thank you ruby

    Quote Originally Posted by r937 View Post
    Code:
    SELECT table2.empcode 
         , table2.empname 
         , table2.rmcode
         , mgr.empname AS rmname 
         , table3.testknowledge 
         , table3.process
      FROM table3
    INNER
      JOIN table2
        ON table2.empcode = table3.empcode
    LEFT OUTER
      JOIN table2 AS mgr
        ON mgr.empcode = table2.rmcode
     WHERE table3.projcode = '1001'
    can u tell me a query without using any joins and just using alias name???

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by sathupadikesavan View Post
    can u tell me a query without using any joins and just using alias name???
    no

    your team leader is mistaken
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jan 2011
    Posts
    4

    Thank you rudy

    thank you rudy ..
    i will use your query with joins

Posting Permissions

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