WITH temps AS ( select pippo.mod_id,sum(pippo.verified) as verified from (
SELECT mod_id,1 as verified from modelle where mod_sesso='F'
UNION ALL
SELECT mod_id,1.00001 as verified from modelle where mod_carnagione=10 AND mod_sesso='F'
UNION ALL
SELECT mod_id,1.00002 as verified from modelle where mod_occhi=5 AND mod_sesso='F'
) as pippo group by pippo.mod_id )
select temps.mod_id,temps.verified,mod_sesso,mod_localita_nascita,mod_nome,mod_tipocapelli,mod_cognome,mod_comune_residenza, mod_codice,mod_altezza,mod_taglia,mod_scarpe,mod_torace,mod_vita,mod_fianchi, DATEDIFF(yy, coalesce(mod_data_nascita,getdate()),getdate()) as eta,mod_comune_residenza,nazioni.lolg_label as nazionalita,mod_nazionalita, appoggi.lolg_id as appoggio_id,appoggi.lolg_label as appoggio,mlCarnagione.mtl_label as carnagione,ml_li_id, mtlOcchi.mtl_label as occhi,mtlCapelli.mtl_label as capelli,mtlLingue.mtl_label as lingua,ml_lcl_id as livellolingua,immagini.Imm_PathFile
from temps
INNER JOIN modelle on temps.mod_id=modelle.mod_id
LEFT join immagini on (Imm_IDModella=temps.mod_id)
LEFT JOIN modelle_appoggi ON ma_mod_id=temps.mod_id
LEFT JOIN MasterTableLocales as mlCarnagione ON (mlCarnagione.mtl_id=mod_carnagione AND mlCarnagione.mtl_language=@lang AND mlCarnagione.mtl_type='carnagione')
LEFT JOIN MasterTableLocales as mtlOcchi ON (mtlOcchi.mtl_id=mod_occhi AND mtlOcchi.mtl_language=@lang AND mtlOcchi.mtl_type='occhi')
LEFT JOIN MasterTableLocales as mtlCapelli ON (mtlCapelli.mtl_id=mod_occhi AND mtlCapelli.mtl_language=@lang AND mtlCapelli.mtl_type='capelli')
LEFT JOIN LocalesLocalitaGeografiche as appoggi ON (appoggi.lolg_id=ma_cod_localita AND appoggi.lolg_lingua=@lang)
LEFT JOIN LocalesLocalitaGeografiche as nazioni ON (nazioni.lolg_id=mod_nazionalita AND nazioni.lolg_lingua=@lang)
LEFT JOIN modelle_lingue ON temps.mod_id=ml_mod_id
LEFT JOIN MasterTableLocales as mtlLingue ON (mtlLingue.mtl_id=ml_li_id AND mtlLingue.mtl_language=@lang AND mtlLingue.mtl_type='lingue')
where modelle.mod_id in
( select top 8 mod_id from ( SELECT mod_id,verified,ROW_NUMBER() OVER (ORDER BY verified desc) AS rn FROM temps ) as pippo WHERE rn >@lastid ) ORDER BY modelle.mod_voto
temp is a Common Table Expression and is used to see which id satisfies a criteria and I used a query composer helper class for create queries in WITH statement....IDs later are united by UNION ALL. For count how many criteria are verified I used a trick..."verified" column is a sum of placeholder as 1,1.0001,10002 etc that I generate with a placeholder in my code.
So you can count how many criteria are verified by using a simple Convert.ToInt32(DataRow["verified"]), before you must know criteria count for compare later...;-)
Now you have your id order by criteria satified...For pagination I used a new feature of SQL Server 2005 ROW_NUMBER() OVER....that permit to associate an counter to your rows. At the end by TOP instruction I had my page size (in my case page sizeis 8).
Later I used a factory to build my objects from data set with all that joins (it was a really complex object)...
Result is that performace are really good and I retrieve a small part of my data every time...using Enterprise Library Policy Application Block will give you more performace too...this is an other advice for give you best performance;-)
Bye
Antonio