Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

SQL Queries

Posted on 2004-05-02
5
Medium Priority
?
428 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 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In this article, I’ll look at how you can use a backup to start a secondary instance for MongoDB.
Among the most obnoxious of Exchange errors is error 1216 – Attached Database Mismatch error of the Jet Database Engine. When faced with this error, users may have to suffer from mailbox inaccessibility and in worst situations, permanent data loss.
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…
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 …
Suggested Courses

564 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