Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2003
    Location
    The Netherlands, Oudkarspel
    Posts
    27

    Unanswered: Select all the company's with just one of the persons

    Hi,

    I have three tables.

    Organisations:
    -----------------------------------
    CompanyID | CompanyName
    -----------------------------------
    1 | CompanyA
    2 | CompanyB
    3 | CompanyC
    -----------------------------------

    Functions:
    ------------------------------------------
    CompanyID | PersonID | FunctionID
    ------------------------------------------
    1 | 1 | A
    2 | 2 | A
    3 | 3 | A
    2 | 1 | A
    3 | 1 | A
    -----------------------------------------

    Persons:
    ----------------------------
    PersonID | PersonName
    ----------------------------
    1 | Peter
    2 | Frank
    3 | Ben
    ----------------------------

    What I want is to run a SELECT query with which I can select all the companys where a person works with FunctionID is A. Now, I can run the folowing query:

    SELECT Organisations.CompanyID, Organisations.CompanyName, Persons. PersonName
    FROM Organisations, Persons, Functions
    WHERE (Organisations.CompanyID = Functions.CompanyID AND Functions.PersonID = Persons.PersonID)
    AND Functions.FunctionID = 'A'

    With this query I get the following result:

    ---------------------------------------------------
    CompanyID | CompanyName | PersonName
    ---------------------------------------------------
    1 | CompanyA | Peter
    2 | CompanyB | Frank
    2 | CompanyB | Peter
    3 | CompanyC | Peter
    3 | CompanyC | Ben
    ---------------------------------------------------

    But this is not exactly what I want. I want a resultset with every company just one time in each column. This is with my query not possible because there are, for some company's, more than one persons with FunctionID A.

    Does anybody have an idea of the query I have to use it this case? Or is this not possible with a query and do I have to use some sort of Stored Procedure?

    J.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select distinct
           Organisations.CompanyID
         , Organisations.CompanyName
      from Organisations
         , Persons
         , Functions
     where Organisations.CompanyID = Functions.CompanyID 
       and Functions.PersonID = Persons.PersonID
       and Functions.FunctionID = 'A'
    rudy
    http://r937.com/

  3. #3
    Join Date
    Jan 2003
    Location
    The Netherlands, Oudkarspel
    Posts
    27
    The DISTINCT function will not work. I allready tried it.

    The DISTINCT function removes all duplicate records, butt there are no duplicate records as I showed in my resultset:

    ---------------------------------------------------
    CompanyID | CompanyName | PersonName
    ---------------------------------------------------
    1 | CompanyA | Peter
    2 | CompanyB | Frank
    2 | CompanyB | Peter
    3 | CompanyC | Peter
    3 | CompanyC | Ben
    ---------------------------------------------------

    I just want a resultset like this:

    ---------------------------------------------------
    CompanyID | CompanyName | PersonName
    ---------------------------------------------------
    1 | CompanyA | Peter
    2 | CompanyB | Frank
    3 | CompanyC | Peter
    ---------------------------------------------------

    Every company must be just once in the resultset and if ther are more than one persons working at that company with the same FunctionID, only one of the must be shown. It does not mather which one.

    J.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you need to clarify your specs

    if there is more than one person at a company, which one would you like to show? this is crucial

    if you have

    2 | CompanyB | Frank
    2 | CompanyB | Peter

    then how did you decide to choose Frank over Peter?????

    you said "It does not mather which one" but that's not good enough for database queries

    give me something to decide on

    rudy

  5. #5
    Join Date
    Jan 2003
    Location
    The Netherlands, Oudkarspel
    Posts
    27
    Hi,

    I understand that I must specify which one to choose. A database must have specific 'orders' to do his job.

    Let's say that the database must pick the first one he finds.

    So, if the resultset of the query I posted in my first post is like this:

    2 | CompanyB | Frank
    2 | CompanyB | Peter

    the database has to show only the first one he sees. So the resultset of the new query must be:

    2 | CompanyB | Frank

    I hope you can do something with this...

    J.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    "Let's say that the database must pick the first one he finds."

    sadly, there is no order in a relational database, so "first" has no meaning

    you must specify a column to sequence on -- then, you can take the first one by that sequence


    rudy

Posting Permissions

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