UNION — webForum

EclipseMedlem sedan juli 20003 825 inlägg

Trådstart18 feb. 2003 10:41#1

Detta är en follow-up från denna tråden.

Jag har modifierat koden till:

' ## SELECT MAILINGLISTS
SQL = " SELECT m.intID, m.strName, 'TRUE' as Subscriber" _
& " FROM mailinglist m, users u " _
& " WHERE EXISTS " _ 
& " (SELECT intID FROM subscribers s WHERE s.intUserID = u.intID AND s.intMailinglistID = m.intID AND s.intUserID = " & request("ID") & ") " _
& " UNION " _
& " SELECT m.intID, m.strName, 'FALSE' " _
& " FROM mailinglist m, users u " _
& " WHERE NOT EXISTS " _
& " (SELECT intID FROM subscribers s WHERE s.intUserID = u.intID AND s.intMailinglistID = m.intID AND s.intUserID = " & request("ID") &");"
set mySQL = Conn.Execute(SQL)
listMailinglist = mySQL.GetRows()

Här ser ni resultatet.
Det är följdaktligen inte superläckert. jag vill ju inte att samma post ska finnas med två gånger! Vad göra??

EclipseMedlem sedan juli 20003 825 inlägg

18 feb. 2003 15:23#2

This is the way

' ## SELECT MAILINGLISTS
SQL = " SELECT m.intID, m.strName, 'TRUE' as Subscriber" _
& " FROM mailinglist m, [b]users u[/b] " _
& " WHERE EXISTS " _ 
& " (SELECT intID FROM subscribers s WHERE s.intUserID = u.intID AND s.intMailinglistID = m.intID AND s.intUserID = " & request("ID") & ") " _
& " UNION " _
& " SELECT m.intID, m.strName, 'FALSE' " _
& " FROM mailinglist m, [b]users u[/b] " _
& " WHERE NOT EXISTS " _
& " (SELECT intID FROM subscribers s WHERE s.intUserID = u.intID AND s.intMailinglistID = m.intID AND s.intUserID = " & request("ID") &");"
set mySQL = Conn.Execute(SQL)
listMailinglist = mySQL.GetRows()

' ## SELECT MAILINGLISTS
SQL = " SELECT m.intID, m.strName, 'TRUE' as Subscriber" _
& " FROM mailinglist m " _
& " WHERE EXISTS " _ 
& " (SELECT intID FROM subscribers s WHERE s.intUserID = u.intID AND s.intMailinglistID = m.intID AND s.intUserID = " & request("ID") & ") " _
& " UNION " _
& " SELECT m.intID, m.strName, 'FALSE' " _
& " FROM mailinglist m " _
& " WHERE NOT EXISTS " _
& " (SELECT intID FROM subscribers s WHERE s.intUserID = u.intID AND s.intMailinglistID = m.intID AND s.intUserID = " & request("ID") &");"
set mySQL = Conn.Execute(SQL)
listMailinglist = mySQL.GetRows()

Nu svarar jag mig själv. Ta bort users u så fungerar det!