fulltext, distinct, order by

I have a problem. I use Constainstable to make a fulltextsearch over 4
tables.
Now I want to order the result set. The problem is that if I insert the
column all_ranks in the Select part I have some double results. That is
because some keywords are 2 times or 3 times related to an expert.
So If I use this statement I have double information:
Select distinct inQuery.all_rank, inQuery.employeeid,
inQuery.firstname, inQuery.familyname, inQuery.Qualifications,
inQuery.Title, inQuery.Profil, inQuery.deptname from experts,
(SELECT e.EMPLID as employeeid, e.FIRST_NAME AS firstname,
e.FAMILY_NAME AS familyname, e.QUALIFICATIONS AS Qualifications,
e.TITLE AS Title, e.LINK AS Profil,
d.NAME AS deptname, ISNULL(exp_rank.[RANK], 0) +
ISNULL(key_rank.[RANK], 0) + ISNULL(area_rank.[RANK],0)
+ ISNULL(theme_rank.[RANK],0) as all_rank FROM dbo.EXPERTS e
inner JOIN
dbo.EXP_KEY ek ON e.EMPLID = ek.EMPLID INNER JOIN
dbo.KEYWORDS k ON k.KEY_ID = ek.KEY_ID inner
JOIN
dbo.EXP_DEPT ed ON ed.EMPLID = e.EMPLID inner
JOIN
dbo.DEPARTMENT d ON d.DEPARTMENT_ID =
ed.DEPARTMENT_ID inner JOIN
dbo.AREA_KEY ak ON ak.KEY_ID = k.KEY_ID inner
JOIN
dbo.AREA a ON a.AREA_ID = ak.AREA_ID inner JOIN
dbo.THEME t ON t.THEME_ID = a.THEME_ID full
outer join
CONTAINSTABLE(EXPERTS, *, '"applied" or "computing"') as exp_rank on
exp_rank.[KEY] = e.emplid full outer join
CONTAINSTABLE(Keywords, *, '"applied" or "computing"') as key_rank on
key_rank.[KEY] = k.key_id full outer join
CONTAINSTABLE(Area, *, '"applied" or "computing"') as area_rank on
area_rank.[KEY] = a.area_id full outer join
CONTAINSTABLE(Theme, *, '"applied" or "computing"') as theme_rank on
theme_rank.[KEY] = t.theme_id
WHERE k.key_id = key_rank.[KEY] or
e.emplid = exp_rank.[KEY] or
a.area_id = area_rank.[KEY] or
t.theme_id = theme_rank.[KEY]) as inQuery
order by inQuery.all_rank desc
What I want to use is a Statement of this kind:
Select distinct inQuery.employeeid, inQuery.firstname,
inQuery.familyname, inQuery.Qualifications, inQuery.Title,
inQuery.Profil, inQuery.deptname from experts,
(SELECT e.EMPLID as employeeid, e.FIRST_NAME AS firstname,
e.FAMILY_NAME AS familyname, e.QUALIFICATIONS AS Qualifications,
e.TITLE AS Title, e.LINK AS Profil,
d.NAME AS deptname, ISNULL(exp_rank.[RANK], 0) +
ISNULL(key_rank.[RANK], 0) + ISNULL(area_rank.[RANK],0)
+ ISNULL(theme_rank.[RANK],0) as all_rank FROM dbo.EXPERTS e
inner JOIN
dbo.EXP_KEY ek ON e.EMPLID = ek.EMPLID INNER JOIN
dbo.KEYWORDS k ON k.KEY_ID = ek.KEY_ID inner
JOIN
dbo.EXP_DEPT ed ON ed.EMPLID = e.EMPLID inner
JOIN
dbo.DEPARTMENT d ON d.DEPARTMENT_ID =
ed.DEPARTMENT_ID inner JOIN
dbo.AREA_KEY ak ON ak.KEY_ID = k.KEY_ID inner
JOIN
dbo.AREA a ON a.AREA_ID = ak.AREA_ID inner JOIN
dbo.THEME t ON t.THEME_ID = a.THEME_ID full
outer join
CONTAINSTABLE(EXPERTS, *, '"applied" or "computing"') as exp_rank on
exp_rank.[KEY] = e.emplid full outer join
CONTAINSTABLE(Keywords, *, '"applied" or "computing"') as key_rank on
key_rank.[KEY] = k.key_id full outer join
CONTAINSTABLE(Area, *, '"applied" or "computing"') as area_rank on
area_rank.[KEY] = a.area_id full outer join
CONTAINSTABLE(Theme, *, '"applied" or "computing"') as theme_rank on
theme_rank.[KEY] = t.theme_id
WHERE k.key_id = key_rank.[KEY] or
e.emplid = exp_rank.[KEY] or
a.area_id = area_rank.[KEY] or
t.theme_id = theme_rank.[KEY]) as inQuery
order by inQuery.all_rank desc
Without the inQuery.all_rank in the Select part because this part
insert the double information.
This is the error Message:
Server: Msg 145, Level 15, State 1, Line 1
ORDER BY items must appear in the select list if SELECT DISTINCT is
specified.
HELP!!!
I can't find a way !!
Thank you very much!
Cheers
Sebastian
Whenever you see an or you should ask yourself is it possible that the
values coming from the or condition could be duplicated.
So if it is a gender thing where there are three cases, M, F, and U
(unknown) there will never be any overlap in the OR condition.
And you can do union alls and get better performance.
If there is an overlap, ie where PK>10 or Gender='M' and you could have
Males with a PK >10 which would be duplicates you would have to do some sort
of funky group by like this:
--I really need to see your data to figure this out though
Select inQuery.all_rank, inQuery.employeeid,
inQuery.firstname, inQuery.familyname, inQuery.Qualifications,
inQuery.Title, inQuery.Profil, inQuery.deptname from experts,
(SELECT e.EMPLID as employeeid, e.FIRST_NAME AS firstname,
e.FAMILY_NAME AS familyname, e.QUALIFICATIONS AS Qualifications,
e.TITLE AS Title, e.LINK AS Profil,
d.NAME AS deptname, sum(ISNULL(exp_rank.[RANK], 0) +
ISNULL(key_rank.[RANK], 0) + ISNULL(area_rank.[RANK],0)
+ ISNULL(theme_rank.[RANK],0)) as all_rank FROM dbo.EXPERTS e
inner JOIN
dbo.EXP_KEY ek ON e.EMPLID = ek.EMPLID INNER JOIN
dbo.KEYWORDS k ON k.KEY_ID = ek.KEY_ID inner
JOIN
dbo.EXP_DEPT ed ON ed.EMPLID = e.EMPLID inner
JOIN
dbo.DEPARTMENT d ON d.DEPARTMENT_ID =
ed.DEPARTMENT_ID inner JOIN
dbo.AREA_KEY ak ON ak.KEY_ID = k.KEY_ID inner
JOIN
dbo.AREA a ON a.AREA_ID = ak.AREA_ID inner JOIN
dbo.THEME t ON t.THEME_ID = a.THEME_ID full
outer join
CONTAINSTABLE(EXPERTS, *, '"applied" or "computing"') as exp_rank on
exp_rank.[KEY] = e.emplid full outer join
CONTAINSTABLE(Keywords, *, '"applied" or "computing"') as key_rank on
key_rank.[KEY] = k.key_id full outer join
CONTAINSTABLE(Area, *, '"applied" or "computing"') as area_rank on
area_rank.[KEY] = a.area_id full outer join
CONTAINSTABLE(Theme, *, '"applied" or "computing"') as theme_rank on
theme_rank.[KEY] = t.theme_id
WHERE k.key_id = key_rank.[KEY] or
e.emplid = exp_rank.[KEY] or
a.area_id = area_rank.[KEY] or
t.theme_id = theme_rank.[KEY]
group by e.EMPLID, e.FIRST_NAME ,
e.FAMILY_NAME, e.QUALIFICATIONS,
e.TITLE, e.LINK,d.NAME) as inQuery
order by inQuery.all_rank desc
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Sebastian" <C_o_z_m_o@.gmx.de> wrote in message
news:1163990663.770428.118290@.m73g2000cwd.googlegr oups.com...
>I have a problem. I use Constainstable to make a fulltextsearch over 4
> tables.
> Now I want to order the result set. The problem is that if I insert the
> column all_ranks in the Select part I have some double results. That is
> because some keywords are 2 times or 3 times related to an expert.
> So If I use this statement I have double information:
> Select distinct inQuery.all_rank, inQuery.employeeid,
> inQuery.firstname, inQuery.familyname, inQuery.Qualifications,
> inQuery.Title, inQuery.Profil, inQuery.deptname from experts,
> (SELECT e.EMPLID as employeeid, e.FIRST_NAME AS firstname,
> e.FAMILY_NAME AS familyname, e.QUALIFICATIONS AS Qualifications,
> e.TITLE AS Title, e.LINK AS Profil,
> d.NAME AS deptname, ISNULL(exp_rank.[RANK], 0) +
> ISNULL(key_rank.[RANK], 0) + ISNULL(area_rank.[RANK],0)
> + ISNULL(theme_rank.[RANK],0) as all_rank FROM dbo.EXPERTS e
> inner JOIN
> dbo.EXP_KEY ek ON e.EMPLID = ek.EMPLID INNER JOIN
> dbo.KEYWORDS k ON k.KEY_ID = ek.KEY_ID inner
> JOIN
> dbo.EXP_DEPT ed ON ed.EMPLID = e.EMPLID inner
> JOIN
> dbo.DEPARTMENT d ON d.DEPARTMENT_ID =
> ed.DEPARTMENT_ID inner JOIN
> dbo.AREA_KEY ak ON ak.KEY_ID = k.KEY_ID inner
> JOIN
> dbo.AREA a ON a.AREA_ID = ak.AREA_ID inner JOIN
> dbo.THEME t ON t.THEME_ID = a.THEME_ID full
> outer join
> CONTAINSTABLE(EXPERTS, *, '"applied" or "computing"') as exp_rank on
> exp_rank.[KEY] = e.emplid full outer join
> CONTAINSTABLE(Keywords, *, '"applied" or "computing"') as key_rank on
> key_rank.[KEY] = k.key_id full outer join
> CONTAINSTABLE(Area, *, '"applied" or "computing"') as area_rank on
> area_rank.[KEY] = a.area_id full outer join
> CONTAINSTABLE(Theme, *, '"applied" or "computing"') as theme_rank on
> theme_rank.[KEY] = t.theme_id
> WHERE k.key_id = key_rank.[KEY] or
> e.emplid = exp_rank.[KEY] or
> a.area_id = area_rank.[KEY] or
> t.theme_id = theme_rank.[KEY]) as inQuery
> order by inQuery.all_rank desc
>
> What I want to use is a Statement of this kind:
>
> Select distinct inQuery.employeeid, inQuery.firstname,
> inQuery.familyname, inQuery.Qualifications, inQuery.Title,
> inQuery.Profil, inQuery.deptname from experts,
> (SELECT e.EMPLID as employeeid, e.FIRST_NAME AS firstname,
> e.FAMILY_NAME AS familyname, e.QUALIFICATIONS AS Qualifications,
> e.TITLE AS Title, e.LINK AS Profil,
> d.NAME AS deptname, ISNULL(exp_rank.[RANK], 0) +
> ISNULL(key_rank.[RANK], 0) + ISNULL(area_rank.[RANK],0)
> + ISNULL(theme_rank.[RANK],0) as all_rank FROM dbo.EXPERTS e
> inner JOIN
> dbo.EXP_KEY ek ON e.EMPLID = ek.EMPLID INNER JOIN
> dbo.KEYWORDS k ON k.KEY_ID = ek.KEY_ID inner
> JOIN
> dbo.EXP_DEPT ed ON ed.EMPLID = e.EMPLID inner
> JOIN
> dbo.DEPARTMENT d ON d.DEPARTMENT_ID =
> ed.DEPARTMENT_ID inner JOIN
> dbo.AREA_KEY ak ON ak.KEY_ID = k.KEY_ID inner
> JOIN
> dbo.AREA a ON a.AREA_ID = ak.AREA_ID inner JOIN
> dbo.THEME t ON t.THEME_ID = a.THEME_ID full
> outer join
> CONTAINSTABLE(EXPERTS, *, '"applied" or "computing"') as exp_rank on
> exp_rank.[KEY] = e.emplid full outer join
> CONTAINSTABLE(Keywords, *, '"applied" or "computing"') as key_rank on
> key_rank.[KEY] = k.key_id full outer join
> CONTAINSTABLE(Area, *, '"applied" or "computing"') as area_rank on
> area_rank.[KEY] = a.area_id full outer join
> CONTAINSTABLE(Theme, *, '"applied" or "computing"') as theme_rank on
> theme_rank.[KEY] = t.theme_id
> WHERE k.key_id = key_rank.[KEY] or
> e.emplid = exp_rank.[KEY] or
> a.area_id = area_rank.[KEY] or
> t.theme_id = theme_rank.[KEY]) as inQuery
> order by inQuery.all_rank desc
>
>
> Without the inQuery.all_rank in the Select part because this part
> insert the double information.
>
> This is the error Message:
> Server: Msg 145, Level 15, State 1, Line 1
> ORDER BY items must appear in the select list if SELECT DISTINCT is
> specified.
> HELP!!!
> I can't find a way !!
> Thank you very much!
> Cheers
> Sebastian
>