Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2002
    Posts
    43

    Unanswered: SQL Query Question

    I am hoping someone can help me. I am trying to do a query to gather some information. My database is set up like:

    person table:

    Person ID
    Last Name
    First Name
    ...

    Customer table:

    Cust_ID (foreign key links to Person ID)
    Location
    Employee ID (foreign key links to employee table)
    ...

    Employee Table:

    Employee ID (foreign key links to Person ID)
    ....

    there is other information in the tables, but this is enough to get what I want. I am trying to write a query to give me the employee name and which customers coorespond to that employee. I need both the employee and customer names as well as the csutomer location. Does anyone have an idea on how to accomplish this.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: SQL Query Question

    Originally posted by cmptrguru7
    I am hoping someone can help me. I am trying to do a query to gather some information. My database is set up like:

    person table:

    Person ID
    Last Name
    First Name
    ...

    Customer table:

    Cust_ID (foreign key links to Person ID)
    Location
    Employee ID (foreign key links to employee table)
    ...

    Employee Table:

    Employee ID (foreign key links to Person ID)
    ....

    there is other information in the tables, but this is enough to get what I want. I am trying to write a query to give me the employee name and which customers coorespond to that employee. I need both the employee and customer names as well as the csutomer location. Does anyone have an idea on how to accomplish this.
    Something like:

    select p.last_name, c.cust_name, c.location
    from person p, employee e, customer c
    where p.person_id = e.person_id
    and e.employee_id = c.employee_id
    order by p.last_name, c.cust_name;

  3. #3
    Join Date
    Feb 2002
    Posts
    43

    Re: SQL Query Question

    Originally posted by andrewst
    Something like:

    select p.last_name, c.cust_name, c.location
    from person p, employee e, customer c
    where p.person_id = e.person_id
    and e.employee_id = c.employee_id
    order by p.last_name, c.cust_name;
    The customer table does not have a customer name. All information about a person is stored in the person table. I am not sure how I can get what I need from this query. I think I am going to have to do a subquery somewhere.

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: SQL Query Question

    Right, didn't spot this part:

    Cust_ID (foreign key links to Person ID)

    You just need to join to Person table again:

    select p.last_name, p2.last_name as cust_name, c.location
    from person p, employee e, customer c, person p2
    where p.person_id = e.person_id
    and e.employee_id = c.employee_id
    and p2.person_id= c.cust_id
    order by p.last_name, p2.last_name;
    Last edited by andrewst; 09-08-03 at 13:45.

  5. #5
    Join Date
    Feb 2002
    Posts
    43

    Re: SQL Query Question

    Originally posted by andrewst
    Right, didn't spot this part:

    Cust_ID (foreign key links to Person ID)

    You just need to join to Person table again:

    select p.last_name, p2.last_name as cust_name, c.location
    from person p, employee e, customer c, person p2
    where p.person_id = e.person_id
    and e.employee_id = c.employee_id
    and p2.person_id= c.cust_id
    order by p.last_name, p2.last_name;
    Thank you that worked!!

Posting Permissions

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