admin管理员组文章数量:1418054
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'
)");
`
- 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
1 Answer
Reset to default 1Both 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
本文标签： sqlProblems selecting multiple columnStack Overflow
版权声明：本文标题：sql - Problems selecting multiple column - Stack Overflow 内容由网友自发贡献，该文观点仅代表作者本人， 转载请联系作者并注明出处：http://www.betaflare.com/web/1745250646a2649804.html， 本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容，一经查实，本站将立刻删除。
发表评论