Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    Regular Coder
    Join Date
    Jan 2004
    Posts
    185
    Thanks
    2
    Thanked 1 Time in 1 Post

    Querying and Foreign Keys

    Hi,

    Say I've got 2 Tables: Supervisors and Employees

    Supervisors might look something like this:
    Code:
    ID	Dept_ID		FName	LName
    1	1		Bob	Smith
    2	1		John	Doe
    3	1		Ken	Jones
    4	2		Sam	West
    5	2		Jim	Brown
    And Employees might look like this:
    Code:
    ID	Super_ID	FName	LName
    1	5		John	Adams	
    2	3		George	Washington
    3	4		Thomas	Jefferson
    4	2		Abe	Lincoln
    5	5		Ron	Reagon
    6	1		John	Kennedy
    7	1		Linden	Johnson
    8	3		James	Carter
    9	2		George	Bush
    10	3		Richard	Nixon
    How would I structure a query that selected all employees whose supervisor had a Dept_ID of 1?

    TYIA

  • #2
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,987
    Thanks
    4
    Thanked 2,660 Times in 2,629 Posts
    Code:
    SELECT e.ID, e.FName, e.LName FROM `Employees` e
    INNER JOIN `Supervisors` s ON (s.ID = e.Super_ID)
    WHERE s.Dept_ID = 1
    I assume that Super_ID is the supervisor's ID.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 

  • Users who have thanked Fou-Lu for this post:

    subhailc (02-26-2009)

  • #3
    Regular Coder
    Join Date
    Jan 2004
    Posts
    185
    Thanks
    2
    Thanked 1 Time in 1 Post
    Thank you.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •