在SQL中将其他表用作WHERE条件 [英] To use other table as a WHERE criteria in SQL

问题描述

我正在尝试搜索具有给定标签的问题.

I am trying to search questions which have a given tag.

如何解决以下问题?

表格

questions          |     tags
-------------------|-----------------
  question_id      |     tag
  title            |     question_id
  was_sent_at_time |

我的代码

    SELECT question_id, title
    FROM questions
    WHERE question_id IN
    ( 
        SELECT question_id
        FROM questions
        ORDER BY was_sent_at_time      // problem here
        DESC LIMIT 50
    ) 
    AND tag IN                         // problem here
    (
        SELECT tag FROM tags
        WHERE tag = $1
        AND WHERE question_id IN (                                                                               
            SELECT question_id
            FROM questions
            ORDER BY was_sent_at_time
            DESC LIMIT 50
        )
    )
    ORDER BY was_sent_at_time
    DESC LIMIT 50;

我跑来跑去

Warning: pg_prepare() [function.pg-prepare]: Query failed: ERROR: syntax error at or near "WHERE" LINE 14: AND WHERE question_id IN ( ^ in /var/www/codes/handlers/searches/handle_questions_by_tag.php on line 30

我显然应该使用JOIN. 但是，我不想将标签作为最终结果的输出.

I apparently should be using JOINs. However, I do not want to get tags as an output to my final result.

推荐答案

尝试:

SELECT q.question_id, q.title
    FROM questions      q
        INNER JOIN tags t ON q.question_id=t.question_id
    WHERE tag = $1 
    ORDER BY q.was_sent_at_time
    DESC LIMIT 50

这篇关于在SQL中将其他表用作WHERE条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！