Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2013
    Posts
    2

    Unanswered: Help Needed, Joining 1 table with 2 other table

    I need the last year sales(lastyearsales), first name, last name and city from the following tables. The relation is as following:

    SalesPerson<->Employee <- EmployeeAddress<-Address
    SalesPerson<->Employee <-Contact

    SalesPerson Table
    1.SalesPersonID
    lastyearsales

    Employee Table
    1.EmployeeID
    2.ContactID

    Contact Table
    2.ContactID
    FirstName
    LastName

    EmployeeAddress Table
    1.EmployeeID
    3.AddressID

    Address
    3.AddressID
    City

    And my answer:

    Select: C1.firstname, C1.lastname, SP.lastyearsales, A.city
    From: SalesPerson as SP join Employee as E1 on Sp.salespersonID = E1.EmployeeID
    Right Join Employee as E2 on E1.EmployeeID=E2.EmployeeID join Address as A on E2.AddressID = A.AddressID
    Right Join Employee Contact as C1 on E1.ContactID = C1.ContactID

    Super new to database stuff... Need help.
    Last edited by Shiro; 10-08-13 at 20:52.

  2. #2
    Join Date
    Oct 2013
    Posts
    2
    After recalling some set theory, I figured I must have goofed the one above but regardless, I will leave it there and I think it works like this instead:

    Select C1.firstname, C1.lastname, sp.lastyearsales, A.city
    From salesperson as sp join employee as e1 on SP.SalesPersonID= E1.EmployeeID join EmployeeAddress as E2 on E1.EmployeeID = E2.EmployeeID join Address as A on E2.AddressID = A.AddressID

    Union

    Select C1.firstname, C1.lastname, sp.lastyearsales, A.city
    From salesperson as sp join employee as e1 on SP.SalesPersonID= E1.EmployeeID join Contact as C1 on E1.ContactID = C1.ContactID

    I can't really run this because I can only do it in campus tomorrow, any help or pointers will be very much appreciated.

  3. #3
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Have a look at Detecting joined records that number only 1. You have basically the same problem.

    Apply the same method of breaking down your problem in simpler steps, and work your way up from those.

    As a first step, install a free database system on your computer.
    Create your tables, populate them with some sample data and start from there on.
    Always determine up front what you expect to get in your result set before you start writing a script.
    Compare both the expected result with the obtained result, tweak when necessary before you proceed.
    1. for each employee, find her last year sales
    2. for each employee, find first name, last name
    3. for each employee, find her city
    4. combine 2 and 3 to get first name, last name and city of each employee
    5. combine 1 and 4 to find last years sales combined with first name, last name and city
    6. (review your script, can you simplify it?)
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

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