Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2003
    Location
    SJ, Costa Rica
    Posts
    48

    Unanswered: complex sql select

    Hi everybody!
    I'm trying to make a sql select(probably not too complex for the people on this forum....)
    Here is an example of my tables:

    tbl_companies tbl_persons
    ----------------- --------------
    id_company --------- id_person
    company_name | name
    id_director -------- | last_name
    id_vp ---------|
    id_secretary ---------|
    id_other ---------|

    the id_director,id_vp, id_secretary and id_other are keys related on the tbl_persons table, I want to select the company name the directors id, first_name and last_name, the VP id, first_name and last_name so on.....
    some people that beacause of the design you can't do it with sql standard...
    is that true???
    regards,
    -eduardo s.m.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You need to join your tbl_persons table to tbl_companies once for each id field you want to look up, and you'll need to use alias for these table joins so the optimizer can tell them apart.
    Use left outer joins to make sure your company record is returned even if there is not a matching person record in one of the joined tables.

    select company_name,
    directors.last_name as director,
    vps.last_name as vp,
    secretaries.last_name as secretary,
    others.last_name as other
    from tbl_companies
    left outer join tbl_persons directors on tbl_companies.id_director = tble_persons.id_person
    left outer join tbl_persons vps on tbl_companies.id_vp = vps.id_person
    left outer join tbl_persons secretaries on tbl_companies.id_secretary = secretaries.id_person
    left outer join tbl_persons others on tbl_companies.id_other = others.id_person

  3. #3
    Join Date
    Mar 2003
    Location
    SJ, Costa Rica
    Posts
    48
    thanks!
    it works great...
    regards,
    -eduardo s.m.

Posting Permissions

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