listing all friends

Question

I have F.FRIENDID, F.MEMBERID columns in FRIENDS table and M.MEMBERID, M.FIRSTNAME, M.LASTNAME in MEMBERS table. I want to list all friends' of signed in member. Issue here is, signed in member's ID can sometimes be in FRIENDID row or MEMBERID row in FRIENDS table. That is because when member adds another member, his or her ID is recorded in FRIENDID field, where other member's ID is recorded in MEMBERID field.

I tried couple of SQL queries. Neither worked. I think this query should have worked but it gives the following error.

SQL = "SELECT F.FRIENDID, F.MEMBERID, F.ACTIVE, M.MEMBERID, M.FIRSTNAME, M.LASTNAME, M.ACTIVE"
SQL = SQL & " FROM FRIENDS F, MEMBERS M"
SQL = SQL & " WHERE (F.FRIENDID OR F.MEMBERID) = "& Session("MEMBERID") &" AND (F.FRIENDID OR F.MEMBERID) = M.MEMBERID AND F.ACTIVE = 1 AND M.ACTIVE = 1"
SQL = SQL & " ORDER BY M.FIRSTNAME, M.LASTNAME ASC"
Set objFriends = objConn.Execute(SQL)

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'OR'.

Answer 1

You can't do: AND (F.FRIENDID OR F.MEMBERID) = M.MEMBERID in SQL.

Change it to be:

AND (F.FRIENDID = M.MEMBERID OR F.MEMBERID = M.MEMBERID)

Answer 2

Did you try this?

SQL = SQL & " WHERE (F.FRIENDID = " & Session("MEMBERID") &
            " OR F.MEMBERID = "& Session("MEMBERID") &
            ") AND (F.FRIENDID = M.MEMBERID OR F.MEMBERID = M.MEMBERID) "&
            "AND F.ACTIVE = 1 AND M.ACTIVE = 1"

Answer 3

You need to use the IN syntax:

WHERE M.MEMBERID IN (F.FRIENDID, F.MEMBERID)
  AND "& Session("MEMBERID") &" IN (F.FRIENDID, F.MEMBERID)

Here is how I'd rewrite your query using ANSI joins:

SELECT f.friendid,
       f.memberid,
       f.active,
       COALESCE(mf.memberid, mm.memberid) 'memberid',
       COALESCE(mf.firstname, mm.firstname) 'firstname',
       COALESCE(mf.lastname, mm.lastname) 'lastname',
       COALESCE(mf.active, mm.active) 'active'
  FROM FRIENDS f
  JOIN MEMBERS mf ON mf.memberid = f.friendid AND mf.active = 1
  JOIN MEMBERS mm ON mm.memberid = f.memberid AND mm.active = 1
 WHERE f.active = 1
   AND "& Session("MEMBERID") &" IN (f.friendid, f.memberid)
 ORDER BY firstname, lastname

Answer 4

You need to look at the syntax of the SQL expression OR can only be used between expressions

e.g X=Z or Y=Z not (X or Y ) = Z

So redo your SQL like that and it should work.. I find it helps to do the SQL at the command line before embedding in VB error messages might be clearer