Solved

SQL Queries

Posted on 2004-05-02
5
401 Views
Last Modified: 2012-08-14
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
Comment
Question by:aahlawat
  • 2
  • 2
5 Comments
 
LVL 15

Expert Comment

by:ishando
ID: 10974365
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
 
LVL 8

Expert Comment

by:annamalai77
ID: 10974595
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
 
LVL 1

Accepted Solution

by:
elatagaw earned 500 total points
ID: 10974774
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
 
LVL 1

Expert Comment

by:elatagaw
ID: 10974780
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
 
LVL 8

Expert Comment

by:annamalai77
ID: 10983495
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

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

I guess that all of us know that caching the data usually increase the performance, but I worried if all of us are aware about the risk that caching the data provides and how to minimize this.  That’s the reason why I decided to write this short art…
This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now