mysql - 如何在UNION查询中的两个表中使用where条件？

Question

我有两个表table1和table2。在两个表中，列几乎相同。我所做的是必须显示两个表中的记录。

就像我在列表页面上一样，我必须显示记录。另外，我必须使用 where 条件。

我尝试了以下查询

$query1='select *,(CASE WHEN is_status =0 THEN "No" ELSE "Yes" END) as status from tbl_lead as l1 UNION select *,(CASE WHEN is_status =0 THEN "No" ELSE "Yes" END) as status from tbl_leadUpload as l2';

现在我必须对表 1 where Leadstatus IN(1,2,3) 和表 2 where Leadstatus IN(1,2,3,4, 7)在同一列表中显示记录。

如何对两个表使用 where 条件？

你能帮我解决这个问题吗？

Answer 1

假设您的 tbl_lead 和 tbl_leadupload 具有相同的列(数字和相应的数据类型)你应该在适当的表中添加每个位置

    $query1='select *
      , CASE WHEN is_status =0 THEN "No" ELSE "Yes" END as status 
    from tbl_lead 
    WHERE leadstatus IN (1,2,3)
    UNION 
    select *
      ,CASE WHEN is_status =0 THEN "No" ELSE "Yes" END
    from tbl_leadUpload
    where leadstatus IN (1,2,3,4,7) ;';'

如果不是，您应该使用显式列，例如:

    $query1='select col1, col2, col3
      , (CASE WHEN is_status =0 THEN "No" ELSE "Yes" END) as status 
    from tbl_lead 
    WHERE leadstatus IN (1,2,3)
    UNION 
    select col1, col2, null
      ,(CASE WHEN is_status =0 THEN "No" ELSE "Yes" END)
    from tbl_leadUpload
    where leadstatus IN (1,2,3,4,7) ;';'