Hente oplysninger i et hug

Noget i retning af:

SELECT *
,(SELECT COUNT(Nick) FROM CHAT_ONLINE
  WHERE ChannelID = CHAT_CHANNEL.ID) as OnlineNicks
FROM CHAT_CHANNEL
ORDER BY ONLINENICKS DESC

strSQL="SELECT *, (SELECT COUNT(Nick) FROM CHAT_ONLINE WHERE ChannelID = CHAT_CHANNEL.ID) as OnlineNicks FROM CHAT_CHANNELORDER BY ONLINENICKS DESC"
set rs = dataConn.execute(strSQL)

Microsoft OLE DB Provider for ODBC Drivers (0x80040E09)
[TCX][MyODBC]You have an error in your SQL syntax near 'SELECT COUNT(Nick) FROM CHAT_ONLINE WHERE ChannelID = CHAT_CHANNEL.ID) as Online' at line 1
/chat/iframe_listChannels.asp, line 39

strSQL = "SELECT CH.Name, CH.Nick, COUNT(ON.Nick) AS OnlineNicks FROM CHAT_CHANNEL AS CH LEFT JOIN CHAT_ONLINE AS ON ON CH.ID=ON.ChannelID GROUP BY CH.Name, CH.Nick ORDER BY OnlineNicks DESC"
set rs = dataConn.execute(strSQL)

Du er nødt til at specificere dine feltnavne fra CHAR_CHANNEL (både lige efter SELECT og efter GROUP BY)

[TCX][MyODBC]You have an error in your SQL syntax near 'ON ON CH.ID=ON.ChannelID GROUP BY CH.Name, CH.Nick ORDER BY OnlineNicks DESC' at line 1

Er den for svær? ;)

Proaccess, er der ikke et "ON" for meget?

prøv:
strSQL = "SELECT CH.Name, CH.Nick, COUNT(ON.Nick) AS OnlineNicks FROM CHAT_CHANNEL AS CH LEFT JOIN CHAT_ONLINE AS ON CH.ID=ON.ChannelID GROUP BY CH.Name, CH.Nick ORDER BY OnlineNicks DESC"
set rs = dataConn.execute(strSQL)

Jeg ændrer lige i mit alias til tabellerne...

strSQL = "SELECT C.Name, C.Nick, COUNT(O.Nick) AS OnlineNicks FROM CHAT_CHANNEL AS C LEFT JOIN CHAT_ONLINE AS O ON C.ID=O.ChannelID GROUP BY C.Name, C.Nick ORDER BY OnlineNicks DESC"
set rs = dataConn.execute(strSQL)

Man må selvfølgelig ikke pludselig kalde en tabel for "on"...  SILLY ME  ;0)

Det virker - takker for hjælpen:

strSQL = "SELECT C.*, COUNT(O.Nick) AS OnlineNicks FROM CHAT_CHANNEL AS C LEFT JOIN CHAT_ONLINE AS O ON C.ID=O.ChannelID GROUP BY C.Name ORDER BY OnlineNicks DESC"
set rs = dataConn.execute(strSQL)

rahp tryk også lige svar :-D

Svar.