MySQL SELECT的结果来自1个表，但排除结果取决于另一个表? [英] MySQL SELECT results from 1 table, but exclude results depending on another table?

问题描述

如果我想从表"messages"中获取结果，但要排除具有"messages_view"中值的行，则其中必须使用哪种SQL查询，其中fields.message = messages_view.id AND messages.deleted = 1 AND messages_view.user = $ somephp变量

What SQL query would I have to use if I want to get the results from a table 'messages' but exclude rows that have the value in 'messages_view' where field messages.message=messages_view.id AND messages.deleted=1 AND messages_view.user=$somephpvariable

用更多的外行术语来说，我有一个消息表，每个 message 用一个'id'表示，还有一个 messages_view 表与一个'message'字段相连.我想获取消息中未被删除的行(来自 messages_view )，用于特定的用户".删除邮件时，已删除" = 1.

In more laymen terms, I have a messages table with each message denoted by an 'id' as well as a messages_view table connected with a 'message' field. I want to get the rows in message that are not deleted (comes from messages_view) for a specific 'user'. 'deleted'=1 when the message is deleted.

这是我当前的SQL查询，只是从中获取值:

Here is my current SQL Query that just gets the values out of :

SELECT * FROM messages WHERE ((m_to=$user_id) 
    OR (m_to=0 AND (m_to_state='' OR m_to_state='$state') 
    AND (m_to_city='' OR m_to_city='$city')))

这是我的桌子的布局:

table: messages
----------------------------
id (INT) (auto increment)
m_from (INT)                     <-- Represents a user id (0 = site admin)
m_to (INT)                       <-- Represents a user id (0 = all users)
m_to_state (VARCHAR)
m_to_city (VARCHAR)

table: messages_view
----------------------------
message (INT)                    <-- Corresponds to messages.id above
user (INT)                       <-- Represents a user id
deleted (INT)                    <-- 1 = deleted

推荐答案

我真的认为它是如此简单:

I really think it's as simple as this:

SELECT * FROM messages WHERE ((m_to=$user_id) 
    OR (m_to=0 AND (m_to_state='' OR m_to_state='$state') 
    AND (m_to_city='' OR m_to_city='$city')))
AND NOT EXISTS (
    SELECT *
    FROM messages_view
    WHERE messages.message = messages_view.id
        AND messages.deleted = 1 
        AND messages_view.user = $somephpvariable
)

这篇关于MySQL SELECT的结果来自1个表，但排除结果取决于另一个表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！