Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393

    Unanswered: Query - House and Persons

    I've got a a table House and a table Person
    A house is contains 1 or 2 persons
    If a house contains 2 persons the first person must be the one
    alphabetically smaller

    It gives me this query :

    select *
    from Person as FirstPerson
    join (select IDHouse,IDPerson,Name From Person) as SecondPerson on FirstPerson.IDHouse = Second.IDHouse
    join (select IDFoyer,NumberOfPersons from House) as TheHouse on FirstPerson.IDFoyer = TheHouse.IDHouse
    where
    (TheHouse.NumberOfPersons = '2' and
    (FirstPerson.Name < SecondPerson.Name and FirstPerson.IDPerson <> SecondPerson.IDPerson) or
    (First.Name = SecondPerson.Name and FirstPerson.IDPerson > SecondPerson.IDPerson)) or
    (TheHouse.NumberOfPersons = '1')


    How can I can it better ???

  2. #2
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393

    Re: Query - House and Persons

    The result must be

    FirstPerson.Name, SecondPerson.Name, TheHouse.IDHouse


    one row for each House

  3. #3
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393

    Re: Query - House and Persons

    briing up mey thread

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    select min(FirstPersons.Name) FirstPerson,
    min(SecondPersons.Name) SecondPerson,
    FirstPersons.HouseID
    from Persons FirstPersons
    left outer join Persons SecondPersons
    on FirstPersons.IDHouse = SecondPersons.IDHouse
    and SecondPersons.Name > FirstPersons.Name
    group by FirstPersons.HouseID

    You will need to link in the house table if you want to include houses with no people in them.

    blindman

  5. #5
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    What about this?

    create table house(id int)
    go
    create table person(id int,houseid int,pname varchar(10))
    go
    insert house values(1)
    insert house values(2)
    insert house values(3)
    insert person values(1,1,'a')
    insert person values(2,1,'b')
    insert person values(3,2,'c')
    insert person values(4,3,'d')
    insert person values(5,3,'e')
    go

    select (select top 1 pname from person where houseid=h.id order by pname) as fperson
    ,isnull((select top 1 pname from person where houseid=h.id
    and id not in (select top 1 id from person where houseid=h.id order by pname)),'nobody') as sperson
    ,id
    from house h

  6. #6
    Join Date
    Oct 2003
    Posts
    706
    Exactly. A "house" is a thing and a "person" is a thing and a "person" occupies no more than one "house."

    You don't want a number_of_persons field in the "house" table nor do you want any field in that table named "occupant_id."

    A more generalized schema would have a table such as occupancy which lists house_id and person_id to reflect a "many to many" relationship between people and houses. This would also allow you to reflect the history of occupancy as a person moves. The question is, what does your application require. Outside of college textbooks, there are no graven slabs of stone being handed down from any mountainside...
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.com

  7. #7
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393
    I've been tricked, I've simplified too much the problem

    I can't use (Select top 1), Group by or Min() because I have about 20 columns in the Select
    But I've kept the idea of adding a clause on a Left Outer Join



    Select First.LastName,First.FirstName,Second.ClientType,T heHouse.City,...
    From Person as First
    Left outer join (Select IDPerson,FirstName,LastName,IDHouse From Person) as Second
    On First.IDHouse = Second.IDHouse and
    (First.LastName + First.FirstName + First.IDPerson <
    Second.LastName + Second.FirstName + Second.IDPerson)
    Join (Select IDHouse,City,NbPersons From House) as TheHouse
    On First.IDHouse = TheHouse.IDHouse
    Where
    (TheHouse.NbOfPersons = '2' and Second.IDPers is not null) Or
    (TheHouse.NbOfPersons = '1')



    But it seems that replacing the <Inner Join> by a <Left outer join>
    to select the second person gives me slower stats

    Why ?

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    An inner join is faster, but wil exclude houses with only one resident.

    blindman

  9. #9
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393
    Will a composite index on
    <LastName + FirstName + IDPerson>

    be faster than separate indexes on those columns

    ?

  10. #10
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Of course it will!

  11. #11
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393
    I've put an composite index on those columns but
    the query doesn't seem to need or use it

    When I look at the "plan" of the query there is no mention of
    the composite index

    I've created this index by selecting all three columns
    and naming it VousConjoint

    I this Ok and why there is no use of my index ?

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Of course it won't!

    Placing a composite index on the LastName, FirstName, and IDPerson fields won't help because you are actually joining on the concatenated of these three strings. This is not a good idea, because not only is it slow and unable to take advantage of a composite index, but the sorting logic can give erroneous results if two people have similiar last names. For instance, someone named John Brown should come before someone name Abe Browning in an alphabetical sort by last name, but the reverse occurs when sorting on the concatenated strings "BrownJohn" and "BrowningAbe".

    To be accurate you should sort and compare on the values separately.

    blindman

    P.S. Adding a comma between your concatenated fields may fix the problem in your logic:

    Where (First.LastName + ',' + First.FirstName + ',' + First.IDPerson < Second.LastName + ',' + Second.FirstName + ',' + Second.IDPerson)
    Last edited by blindman; 12-16-03 at 09:41.

  13. #13
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Try this strategy:

    Add a calculated field to your Persons table that concatenates your LastName + ',' + FirstName + ',' + PersonID, and then index that field. Then this query should get you the information you want:

    select House.*, Person1.*, Person2.*
    from House
    inner join
    (select IDHouse,
    Min(PrimaryResidents.FullName) PrimaryFullName,
    Min(SecondaryResidents.FullName) SecondaryFullName
    from Persons PrimaryResidents
    left outer join Persons SecondaryResidents
    on PrimaryResidents.HouseID = SecondaryResidents.HouseID
    and PrimaryResidents.FullName < SecondaryResidents.FullName) Residents
    on House.HouseID = Residents.HouseID
    inner join Persons Person1 on Residents.PrimaryFullName = Person1.FullName
    left outer join Persons Person2 on Residents.SecondaryFullName = Person2.FullName

    blindman

  14. #14
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393
    the calculated field must be a new column in the table person ?

  15. #15
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Yes.

Posting Permissions

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