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 ?The result must be

FirstPerson.Name, SecondPerson.Name, TheHouse.IDHouse

one row for each House|||briing up mey thread|||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|||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|||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...|||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 ?|||An inner join is faster, but wil exclude houses with only one resident.

blindman|||Will a composite index on
<LastName + FirstName + IDPerson>

be faster than separate indexes on those columns

?|||Of course it will!|||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 ?|||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)|||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|||the calculated field must be a new column in the table person ?|||Yes.|||than I can't the structure of the table doesn't depend on me
too much programs are related to the SQL Server database
and there's also the replication + the big DB2 database structure

each time I demand a modification on the structure
the boss begins hillbilly talking|||Adding a calculated field should not be a big deal. It's not much different than adding an index.

Another alternative might be to create an indexed view that contains your concatenated values. There are restrictions on what types of views can be indexed, but I think yours would qualify. Theoretically you would not even need to reference the view in your query. The optimizer should know about it's existence and include it as a resource automatically.

Otherwise, run the query without the index and tell your boss why his policies result in slow execution times. Try to use short, simple words and phrases so that he will understand.

blindman|||i've read one indexes on views and tried it with no satisfying results
but i'm going to re-read BOL to see if i've missed something

just for info this is a part of the real query
SYBASE Version

=======================================
select '' as Prescripteur,
String(LeFoyer.BLOQUE) as ServiceClientBloquant,'0' as Entite,
String(LeFoyer.IDFoyer) as IDFoyer,
LeFoyer.SSSECTR as SStr,
LeFoyer.PTFAPC as PTFAPC,
LeFoyer.PersPTF as PersPTFFoyer,
LeFoyer.Voie,
LeFoyer.CpltAdr as ComplementAdresse,
LeFoyer.CDPOST as CodePostal,
LeFoyer.BurDist as Commune,
Trim(LeFoyer.Commune) as BureauDistributeur,
TypeEntite(LeFoyer.Typclpr) as TypeFoyer,
String(Premiere.IDPers) as IDVous,
Trim(Premiere.Nom) as NomVous,
Trim(Premiere.Prenom) as PrenomVous,
Premiere.Typclpr as TypeVous,
Premiere.PersPTF as PersPTFVous,
Premiere.MarchEntr as MarcheVous,
Conjoint(LeFoyer.NbPersPrinc,String(Deuxieme.IDPer s)) as IDConjoint,
Conjoint(LeFoyer.NbPersPrinc,Trim(Deuxieme.Nom)) as NomConjoint,
Conjoint(LeFoyer.NbPersPrinc,Trim(Deuxieme.Prenom) ) as PrenomConjoint,
Conjoint(LeFoyer.NbPersPrinc,Deuxieme.Typclpr) as TypeConjoint,
Conjoint(LeFoyer.NbPersPrinc,Deuxieme.PersPTF) as PersPTFConjoint,
Conjoint(LeFoyer.NbPersPrinc,Deuxieme.MarchEntr) as MarcheConjoint,
(if LeFoyer.NbPersPrinc = '001' then NomComplet(NomVous,PrenomVous) else NomComplet(NomVous,PrenomVous) || ' / ' ||
NomComplet(NomConjoint,PrenomConjoint) endif) as Foyer,
(if MarcheVous = '1' or MarcheConjoint = '1' then 1 else 0 endif) as MarcheFoyer,
(if Premiere.TYPCLPR = '04' then null else Premiere.DTNPAI endif) as DTNPAIVous,
(if LeFoyer.NbPersPrinc = '001' then null else(if Deuxieme.TYPCLPR = '04' then null else Deuxieme.DTNPAI endif) endif) as DTNPAIConjoint,
(if IsNull(LeFoyer.BurDist,'',LeFoyer.BurDist) = IsNull(Premiere.BurDist,'',Premiere.BurDist) and IsNull(LeFoyer.Voie,'',LeFoyer.Voie) =
IsNull(Premiere.Voie,'',Premiere.Voie) and IsNull(LeFoyer.Commune,'',LeFoyer.Commune) = IsNull(Premiere.Commune,'',Premiere.Commune) and
IsNull(LeFoyer.CpltAdr,'',LeFoyer.CpltAdr) = IsNull(Premiere.CpltAdr,'',Premiere.CpltAdr) and IsNull(LeFoyer.CdPost,'',LeFoyer.CdPost) =
IsNull(Premiere.CdPost,'',Premiere.CdPost) then DTNPAIVous else null endif) as NPAIParVous,
(if IsNull(LeFoyer.BurDist,'',LeFoyer.BurDist) = IsNull(Deuxieme.BurDist,'',Deuxieme.BurDist) and IsNull(LeFoyer.Voie,'',LeFoyer.Voie) =
IsNull(Deuxieme.Voie,'',Deuxieme.Voie) and IsNull(LeFoyer.Commune,'',LeFoyer.Commune) = IsNull(Deuxieme.Commune,'',Deuxieme.Commune) and
IsNull(LeFoyer.CpltAdr,'',LeFoyer.CpltAdr) = IsNull(Deuxieme.CpltAdr,'',Deuxieme.CpltAdr) and IsNull(LeFoyer.CdPost,'',LeFoyer.CdPost) =
IsNull(Deuxieme.CdPost,'',Deuxieme.CdPost) then DTNPAIConjoint else null endif) as NPAIParConjoint,
(if NPAIParVous is null then(if NPAIParConjoint is null then null else NPAIParConjoint endif)
else(if NPAIParConjoint is null then NPAIParVous else(if NPAIParVous > NPAIParConjoint then
NPAIParVous else NPAIParConjoint endif) endif)
endif) as NPAI,
LeFoyer.TYPCLPR,LeFoyer.PERSPTF,LeFoyer.SSSECTR,Le Foyer.CODDEV,LeFoyer.MNTRVFR,LeFoyer.MNTRVEU,LeFoy er.ORIGINE,LeFoyer.NBENFNT,
LeFoyer.NM1RCMDT,LeFoyer.NM2RCMDT,LeFoyer.PN1RCMDT ,LeFoyer.PN2RCMDT from
dbo.V74PERS0 as Premiere join
(select IDPers,Nom,Prenom,Qualite,Typclpr,IDFoyer,PERSPTF, (if TYPCLPR = '04' then null else DTNPAI endif) as DTNPAI,Voie,Commune,Burdist,CpltAdr,
CdPost,MARCHENTR from dbo.V74PERS0 where Typpers || Suptechn = '10') as Deuxieme(IDPers,Nom,Prenom,Qualite,Typclpr,IDFoyer ,PERSPTF,DTNPAI,Voie,
Commune,Burdist,CpltAdr,CdPost,MARCHENTR) on Premiere.IDFoyer = Deuxieme.IDFoyer join
(select TypSup,IDFoyer,Voie,Commune,BurDist,CDPOST,Typclpr ,CpltAdr,PersPTF,SSSECTR,NBPERSPRINC,BLOQUE,PTFAPC ,CODDEV,MNTRVFR,MNTRVEU,ORIGINE,NBENFNT,NM1RCMDT,
NM2RCMDT,PN1RCMDT,PN2RCMDT from dbo.V74FOYE0) as LeFoyer(TypSup,IDFoyer,Voie,Commune,BurDist,CDPOST ,Typclpr,CpltAdr,PersPTF,SSSECTR,
NBPERSPRINC,BLOQUE,PTFAPC,CODDEV,MNTRVFR,MNTRVEU,O RIGINE,NBENFNT,NM1RCMDT,NM2RCMDT,PN1RCMDT,PN2RCMDT ) on Premiere.IDFoyer = LeFoyer.IDFoyer where
Premiere.Typpers || Premiere.Suptechn = '10' and
((LeFoyer.NbPersPrinc || LeFoyer.TypSup = '002 ' and
(Premiere.TYPCLPR || Premiere.PERSPtf || Premiere.Qualite || Premiere.Nom || Premiere.Prenom <
Deuxieme.TYPCLPR || Deuxieme.PERSPtf || Deuxieme.Qualite || Deuxieme.Nom || Deuxieme.Prenom and Premiere.IDPers <> Deuxieme.IDPers) or
(Premiere.TYPCLPR || Premiere.PERSPtf || Premiere.Qualite || Premiere.Nom || Premiere.Prenom =
Deuxieme.TYPCLPR || Deuxieme.PERSPtf || Deuxieme.Qualite || Deuxieme.Nom || Deuxieme.Prenom and Premiere.IDPers > Deuxieme.IDPers)) or
(LeFoyer.NbPersPrinc || LeFoyer.TypSup = '001 '))|||it's horrible 'cause i've got to calculate lot's of fields
before connecting my Grid "via" Ado-ODBC
in my program.

i'm a bit sick of this query
- gaaah - i'm in agony
going home...

thx for your help Mr US-Ohio-BlindMan|||It still will be faster if you put IDHouse in front of LastName, because the join starts with it.|||rdjabarov

what do you mean, "IDHouse in front of LastName"