如何起诉子查询

Question

  $logged = $_SESSION['logged'];

  $construct =  "SELECT child.* 
                 FROM products child 
                 LEFT JOIN products parent on parent.sid=child.sid 
                 WHERE parent.id =
                   '(SELECT productid 
                     FROM subscribed 
                     WHERE username=\"$logged\")' 
                 AND parent.keyword != child.name 
                 ORDER BY child.id DESC";  

我无法让子查询工作，但得到了错误的结果。父查询很好，因为我以前用过它。

我希望子查询从表“订阅”中获取所有产品 ID，其中 username=$logged。

这就是我想要的，但在一个查询中，检查 WHERE 子句是否存在差异。

$construct =  "SELECT child.* 
               FROM products child 
               LEFT JOIN products parent on parent.sid=child.sid 
               WHERE parent.id= 
                 'SUB QUERY RESULT 1' 
               AND parent.keyword != child.name 
               ORDER BY child.id DESC"; 

$construct =  "SELECT child.* 
               FROM products child 
               LEFT JOIN products parent on parent.sid=child.sid 
               WHERE parent.id=
                 'SUB QUERY RESULT 2' 
               AND parent.keyword != child.name 
               ORDER BY child.id DESC"; 

$construct =  "SELECT child.* 
               FROM products child 
               LEFT JOIN products parent on parent.sid=child.sid 
               WHERE parent.id=
                 'SUB QUERY RESULT 3' 
               AND parent.keyword != child.name 
               ORDER BY child.id DESC"; 

如何在一个查询中执行此操作而不循环查询。我还需要子查询吗？

Answer 1

您可以使用内部联接来执行您想要的操作：

 $construct = "SELECT child.*         
   FROM products child         
   LEFT JOIN products parent on parent.sid=child.sid         
   INNER JOIN subscibed s ON (s.productid = parent.id) 
   WHERE s.username = '$logged' 
     AND parent.keyword != child.name         
   ORDER BY child.id DESC";  

如果您希望将三个查询合并为一个，请执行以下操作：

$construct =  "SELECT child.*              
           FROM products child              
               LEFT JOIN products parent on (parent.sid = child.sid)              
           WHERE parent.id IN             
                 (SELECT productid FROM table1 WHERE username= '$logged'
                UNION 
                  SELECT productid FROM table2 WHERE something = '$somethingelse'
                UNION 
                  SELECT productid FROM table3 WHERE otherthing = '$otherstuff'
                 )              
           AND parent.keyword != child.name              
           ORDER BY child.id DESC";