?
Solved

SQL Queries

Posted on 2004-05-02
5
Medium Priority
?
419 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 1000 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Azure Functions is a solution for easily running small pieces of code, or "functions," in the cloud. This article shows how to create one of these functions to write directly to Azure Table Storage.
In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

771 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