Put movie IDs into the lists:

SQLmatch = "select f1.filmid , count(f1.filmid) as popularity from filmkarakter f1 where ((f1.filmid NOT IN ("&filmmatch1&","&filmmatch2&","&filmmatch3&")) AND (f1.brugerid <> "&brugernr&") AND f1.karakter=6) and f1.brugerid in (Select f2.brugerid from filmkarakter f2 where (f2.filmid IN ("&filmmatch1&","&filmmatch2&","&filmmatch3&") AND f2.karakter=6 AND f2.brugerid <> "&brugernr&")) group by f1.filmid order by 2 desc"

It gives me the same movie more times.
I'm testing it with brugerid=4


My table:

id      filmid      karakter      brugerid
9      1      6      4
17      3      6      4
14      1      6      2
13      2      6      2
12      3      6      2
11      2      6      3
18      1      6      3
3      2      6      5
1      1      6      5
6      3      6      5
2      3      5      8
5      2      5      7
15      1      4      13
7      2      4      9
4      3      3      6
10      2      3      12
8      2      2      11
16      1      0      14

what filmid do you substitute?
with your values I have the following results:

in (1,2,3) -> no rows;
in (1,2) -> (3,2);
in (1) -> (2,3),
              (3,2);
in (2) -> (1,3),
              (3,2);
in (3) -> (1,2),
              (2,2);
in (2,3) -> (1,3);
in (1,3) -> (2,3);

what's unexpected here?

in(1,2,3) -> no rows no rows (aka 2 times)
in(1,2) -> (3) and (3)
in(1) -> (2,3) and (2,3)



can u see the pattern? it gives me the result 2 times

I don't get it.
single query can't return "no rows" twice!

in (1,2) returns filmid = 2 and popularity = 3

in (1) returns two rows
filmid = 2 and popularity = 3
and
filmid = 3 and popularity = 2

Seems to be quite correct, doesn't it?

Have you put the filmmatch list into both places of filmid filter occurences?

filmmatch1=1
filmmatch2=2


SQLmatch = "select f1.filmid , count(f1.filmid) as popularity from filmkarakter f1 where ((f1.filmid NOT IN ("&filmmatch1&","&filmmatch2&")) AND (f1.brugerid <> "&brugernr&") AND f1.karakter=6) and f1.brugerid in (Select f2.brugerid from filmkarakter f2 where (f2.filmid IN ("&filmmatch1&","&filmmatch2&") AND f2.karakter=6 AND f2.brugerid <> "&brugernr&")) group by f1.filmid order by 2 desc"


I use it in a loop and it gives me a print with filmid=3  twice

likedon,
agregate query can't return the same grouping column value more than once. Most likely there is something wrong with loop. Do you fetch from recordset until eof? Or you use some other way?

It's pretty long but here goes:


SQLkar = "select filmid from filmkarakter WHERE brugerid="&brugernr&" ORDER BY ID"
Set RSkar = Server.CreateObject("ADODB.Recordset")
RSkar.CursorLocation = 3
RSkar.CursorType = 3
RSkar.ActiveConnection = Conn
RSkar.Open SQLkar, Conn, 1, 3
If NOT (RSkar.BOF AND RSkar.EOF) Then
do until RSkar.eof

filmmatch1=1
filmmatch2=2


SQLmatch = "select f1.filmid , count(f1.filmid) as popularity from filmkarakter f1 where ((f1.filmid NOT IN ("&filmmatch1&","&filmmatch2&")) AND (f1.brugerid <> "&brugernr&") AND f1.karakter=6) and f1.brugerid in (Select f2.brugerid from filmkarakter f2 where (f2.filmid IN ("&filmmatch1&","&filmmatch2&") AND f2.karakter=6 AND f2.brugerid <> "&brugernr&")) group by f1.filmid order by 2 desc"

Set RSmatch = Server.CreateObject("ADODB.Recordset")
RSmatch.CursorLocation = 3
RSmatch.CursorType = 3
RSmatch.ActiveConnection = Conn
RSmatch.Open SQLmatch, Conn, 1, 3
If NOT (RSmatch.BOF AND RSmatch.EOF) Then
do until RSmatch.eof
filmid=RSmatch("filmid")

SQLmatch2 = "SELECT Titel FROM video WHERE FilmID="&filmid&" ORDER BY FilmID"
Set RSmatch2 = Server.CreateObject("ADODB.Recordset")
RSmatch2.CursorLocation = 3
RSmatch2.CursorType = 3
RSmatch2.ActiveConnection = Conn
RSmatch2.Open SQLmatch2, Conn, 1, 3
titel=RSmatch2("Titel")
RSmatch2.close
Set RSmatch2 = Nothing
%>

<tr><td class="blaa10"><a href="film.asp?film=<%=filmid%>" class="mainboxlinks"><%=titel%></a></td></tr>  
<tr><td height="3"><img src="grafik/space.gif" height="1"></td></tr>

<%RSmatch.movenext
loop
ELSE%>
<tr><td class="blaa10">Ikke nok oplysninger endnu.</td></tr>  
<%
End If
RSmatch.close
Set RSmatch = Nothing
%>
<%RSkar.movenext
loop
ELSE%>
<tr><td class="blaa10">Ikke nok oplysninger endnu.</td></tr>  
<%
End If
RSkar.close
Set RSkar = Nothing
%>

Yes that sems to do it. It seems to work now, testing.