I have the following SQL query, which works for me on selecting all alerts which belongs to me or which I need to see from people am connected to in FRIENDS TABLE

----TABLE USERS-----
ID  ACCOUNT
1   80001
2   90001
3   70001

----TABLE FRIENDS-------
ID ACCOUNT  TO_ACCOUNT
1    80001    70001

----TABLE ALERTS-------
ID  ACCOUNT  ALERT  ALERTID
1   80001            Hi     XD3000

SELECT snd.ACCOUNT
FROM ALERTS AS m
JOIN USERS AS snd ON snd.ACCOUNT = m.ACCOUNT
WHERE snd.ACCOUNT IN (
  SELECT ACCOUNT
  FROM FRIENDS
  WHERE ACCOUNT = '80001' OR TO_ACCOUNT = '70001'
)
UNION
SELECT snd.ACCOUNT
FROM ALERTS AS m
JOIN USERS AS snd ON snd.ACCOUNT = m.ACCOUNT
WHERE snd.ACCOUNT IN (
  SELECT TO_ACCOUNT
  FROM FRIENDS
  WHERE TO_ACCOUNT = '70001' OR ACCOUNT = '80001'
)");

Now the problem is when I try to add this so that I can stop seeing all alerts which blocked by me, its not working

----TABLE ALERTS_BLOCKED-------
ID  ACCOUNT    ALERTID
1     80001         XD3000

SELECT snd.ACCOUNT
FROM ALERTS AS m
JOIN USERS AS snd ON snd.ACCOUNT = m.ACCOUNT
WHERE snd.ACCOUNT IN (
  SELECT ACCOUNT
  FROM FRIENDS
  WHERE ACCOUNT = '80001' OR TO_ACCOUNT = '70001'
)
AND WHERE m.ALERTID NOT IN (
  SELECT ALERTID
  FROM ALERTS_BLOCKED
  WHERE ACCOUNT = '80001'
)
UNION
SELECT snd.ACCOUNT
FROM ALERTS AS m
JOIN USERS AS snd ON snd.ACCOUNT = m.ACCOUNT
WHERE snd.ACCOUNT IN (
  SELECT TO_ACCOUNT
  FROM FRIENDS
  WHERE TO_ACCOUNT = '70001' OR ACCOUNT = '80001'
)
AND WHERE m.ALERTID NOT IN (
  SELECT ALERTID
  FROM ALERTS_BLOCKED
  WHERE ACCOUNT = '70001'
)");
`

I have the following SQL query, which works for me on selecting all alerts which belongs to me or which I need to see from people am connected to in FRIENDS TABLE

----TABLE USERS-----
ID  ACCOUNT
1   80001
2   90001
3   70001

----TABLE FRIENDS-------
ID ACCOUNT  TO_ACCOUNT
1    80001    70001

----TABLE ALERTS-------
ID  ACCOUNT  ALERT  ALERTID
1   80001            Hi     XD3000

SELECT snd.ACCOUNT
FROM ALERTS AS m
JOIN USERS AS snd ON snd.ACCOUNT = m.ACCOUNT
WHERE snd.ACCOUNT IN (
  SELECT ACCOUNT
  FROM FRIENDS
  WHERE ACCOUNT = '80001' OR TO_ACCOUNT = '70001'
)
UNION
SELECT snd.ACCOUNT
FROM ALERTS AS m
JOIN USERS AS snd ON snd.ACCOUNT = m.ACCOUNT
WHERE snd.ACCOUNT IN (
  SELECT TO_ACCOUNT
  FROM FRIENDS
  WHERE TO_ACCOUNT = '70001' OR ACCOUNT = '80001'
)");

Now the problem is when I try to add this so that I can stop seeing all alerts which blocked by me, its not working

----TABLE ALERTS_BLOCKED-------
ID  ACCOUNT    ALERTID
1     80001         XD3000

SELECT snd.ACCOUNT
FROM ALERTS AS m
JOIN USERS AS snd ON snd.ACCOUNT = m.ACCOUNT
WHERE snd.ACCOUNT IN (
  SELECT ACCOUNT
  FROM FRIENDS
  WHERE ACCOUNT = '80001' OR TO_ACCOUNT = '70001'
)
AND WHERE m.ALERTID NOT IN (
  SELECT ALERTID
  FROM ALERTS_BLOCKED
  WHERE ACCOUNT = '80001'
)
UNION
SELECT snd.ACCOUNT
FROM ALERTS AS m
JOIN USERS AS snd ON snd.ACCOUNT = m.ACCOUNT
WHERE snd.ACCOUNT IN (
  SELECT TO_ACCOUNT
  FROM FRIENDS
  WHERE TO_ACCOUNT = '70001' OR ACCOUNT = '80001'
)
AND WHERE m.ALERTID NOT IN (
  SELECT ALERTID
  FROM ALERTS_BLOCKED
  WHERE ACCOUNT = '70001'
)");
`

• sql
• mysql

Share Improve this question Follow asked Feb 3 at 3:26 majo majomajo majo 3111 silver badge33 bronze badges 1

• Both queries are syntactically incorrect because of the trailing ") and the AND WHERE clause , the correct syntax for multiple parts to a where clause is simply AND .please review the manual – P.Salmon Commented Feb 3 at 3:28

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 1

Both queries are syntactically incorrect because of the trailing ") and the AND WHERE clause , the correct syntax for multiple parts to a where clause ( IN THIS CASE ) is simply AND .please review the manual https://dev.mysql/doc/refman/8.4/en/expressions.html

SELECT snd.ACCOUNT
FROM ALERTS AS m
JOIN USRS AS snd ON snd.ACCOUNT = m.ACCOUNT
WHERE snd.ACCOUNT IN (
  SELECT ACCOUNT
  FROM FRIENDS
  WHERE ACCOUNT = '80001' OR TO_ACCOUNT = '70001'
)
AND  m.ALERTID NOT IN (
  SELECT ALERTID
  FROM ALERTS_BLOCKED
  WHERE ACCOUNT = '80001'
)
UNION
SELECT snd.ACCOUNT
FROM ALERTS AS m
JOIN USRS AS snd ON snd.ACCOUNT = m.ACCOUNT
WHERE snd.ACCOUNT IN (
  SELECT TO_ACCOUNT
  FROM FRIENDS
  WHERE TO_ACCOUNT = '70001' OR ACCOUNT = '80001'
)
AND  m.ALERTID NOT IN (
  SELECT ALERTID
  FROM ALERTS_BLOCKED
  WHERE ACCOUNT = '70001'
);

https://dbfiddle.uk/gxwR6S2b