Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL Queries

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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
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…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

636 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