Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 441
  • Last Modified:

SQL Queries

The table below is my table and I can't figure out how to generate a report that has four columns displaying employee names, job title, supervisor’s name and supervisor’s job title. SSSN is Supervisors ID and ESSN is employees.



SSSN      ESSN      First Name      Last Name      Address      Email Name      Job Title Code      Status      Date Hired      Salary
111-11-1111      224-55-4598      Harry      Catone      4 Mill Street      hc@hvc.rr.com      6            5/17/1998      $37,000.00
111-11-1111      125-35-6452      Laxman      Chudry      3 Andera Market      lc@msn.com      4            3/2/1995      $33,000.00
111-11-1111      101-21-2222      Liz      Clayton      15Drynet place      lc@pioneer.com      7      N      9/14/1999      $39,000.00
111-11-1111      100-22-2545      Larry      Hanks      1 Mary Lane      lh@hotmail.com      3      E      1/5/2002      $29,000.00
111-11-1111      100-12-2145      Jack      Hanson      9 Meryl street      JH@aol.com      5      E      5/1/1997      $35,000.00
111-11-1111      125-22-5525      Perry      Henry      3 Floor street      ph1@railroad.com      7      N      2/10/1990      $37,000.00
111-11-1111      103-22-1452      Happy      Hudson      5 Sony Derive      hh@p.com      9      E      1/1/1994      $43,000.00
111-11-1111      121-54-8852      Jack      Jackson      2 Ellen street      jj@yahoo.com      1      E      1/1/2001      $27,000.00
111-11-1111      124-22-5522      Arun      Jackson      5 Merry street      AJ@netscape.net      1      E      4/1/2004      $27,000.00
111-11-1111      122-25-2222      Hall      Jones      2 Jack street      HJ@msn.com      2      E      10/5/2003      $27,000.00
111-11-1111      114-00-2524      Patrick      Jones      2 Rainway drive      pj@pioneer.com      10      N      1/1/2004      $52,000.00
111-11-1111      105-23-5458      Rishi      Kesh      5 Hamari Street      rk@japur.net      5      E      1/1/1998      $33,000.00
111-11-1111      201-22-3354      Jerry      Mahoney      1 Ellen Street      JM@yahoo.com      2      E      11/11/1995      $27,000.00
111-11-1111      110-11-2525      Josh      Main      2 Minus street      jm@yahoo.com      2      E      5/11/1994      $27,000.00
111-11-1111      222-11-1220      Pack      Man      11 Watson Drive      PM@aol.com                  1/1/1997      $36,000.00
111-11-1111      114-14-1112      Railly      Martinez      10 Pillow lane      RM@netscape .net      1      E      1/1/2003      $25,000.00
111-11-1111      112-11-5254      Jason      Matwork      3 Sorry lane      jm@pioneer.com      8      E      1/1/2001      $39,000.00
111-11-1111      245-22-5254      Ram      Mohan      24 Lake street      RM@bs.com                  1/14/2004      $28,000.00
111-11-1111      121-02-2214      Chris      Moon      18 moon Street      cm@ao.com      10      N      5/18/2002      $52,000.00
111-11-1111      200-12-2223      Carrie      Moore      11 cary lane      cm@msn.com      8      E      2/1/2004      $39,000.00
111-11-1111      131-25-2151      John      Perk      21 lawn drive      jp@yahoo.com      9      E      3/20/2002      $51,000.00
111-11-1111      125-23-5125      Ram      Prasad      2 Jyoti road      RP@home.com      3      E      5/11/1997      $29,000.00
111-11-1111      121-21-1211      Jack      Rand      12 Holly Road      jr@is.com                  5/7/2000      $30,000.00
111-11-1111      401-22-5254      Henry      Rocks      4 Smith Drive      HR@hotmail.com      5      E      4/3/1997      $33,000.00
111-11-1111      152-45-6477      Maninder      Singh      4 Prakask lane      ms@india.com      3      E      2/14/2003      $29,000.00
111-11-1111      211-22-1120      Larrry      Smith      5 lake drive      ls@aol.com      3      E      5/8/1973      $29,000.00
111-11-1111      121-11-1252      Jeramy      Watsan      15 Sheet lane      rwat@yahoo.com      3      E      1/1/1995      $29,000.00
111-11-1111      232-51-1252      Mary      Watson      10 Perry Drive      MW@msn.com      4      N      2/4/1996      $31,000.00
0
aahlawat
Asked:
aahlawat
  • 2
  • 2
1 Solution
 
ishandoCommented:
normally you would do something like

SELECT e.first_name, e.last_name, e.job_title, s.first_name, s.last_name, s.job_title
FROM table e LEFT OUTER JOIN table s
  on (s.essn = e.sssn);

But with the data you have shown here you would only get the employees' data out, because there is no employee with an ESSN = 111-11-1111

0
 
annamalai77Commented:
hi there

kindly help us by giving the table names and column names and tell ur requirement clearly. as from the data given i am not able to figure out what ur looking for.

regards
annamalai
0
 
elatagawCommented:
i suppose you have two other tables for suppervisor and job titles.

i'm not sure for the SQL syntax but just try the statement below this will give you a list of what you want:

select e.last_name, e.first_name, j.jobtitle, s.sup_name from e inner join j on e.job_title_code = j.job_title_code
inner join s on e.sssn = s.sssn

good luck!
0
 
elatagawCommented:
additional comments e for the table you mentioned, j for the job titles and s for the supervisor table.

if you got no problem with that statement you can make any change in anyway you want...
0
 
annamalai77Commented:
select a.*, b.*
from job a, supervisor b
where a.job_title_code = b.job_title_code
and     a.sssn = b.sssn(+)

or
select a.*, b.*
from job a, supervisor b
where a.job_title_code = b.job_title_code
and     a.sssn(+) = b.sssn

either one of the query should work.

my dear friend, in ur future questions, kindly mention the table name and ur requirement clearly as i am not able to get what ur lookin for.

regards
annamalai

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now