在基于PHP的MySQL查询中使用AND而不是JOIN - Use of AND instead of JOIN with PHP based MySQL query

Use of AND instead of JOIN with PHP based MySQL query

本文关键字：AND JOIN PHP 查询 MySQL | 更新日期: 2023-12-24

据我所知，AND不能用于指定两个不同的表，但有没有其他方法可以简单地查询两个不同表中完全相同的列名？

目前我有一个PHP变量：

define('TB_RESI', 'RESI');

下面是一个类似的查询。我还需要查询一个名为RESI的具有相同列的表。。。实现这一目标的最佳方式是什么？

当前查询：

$sql = 'SELECT PictureCount AS PIC_COUNT, DATE(PictureModifiedDateTime) AS FILE_DATE, ListingRid AS HOME_ID 
            FROM ' . TB_RESI . 
            ' WHERE (COUNTY = "Lee") AND (
                ListingOfficeMLSID = "PREFP" OR 
                ListingOfficeMLSID = "PREFP2" OR 
                ListingOfficeMLSID = "PREFP3" OR 
                ListingOfficeMLSID = "PREFP4" OR 
                ListingOfficeMLSID = "PREFP6"
            ) 
            AND (PictureCount >= 4) 
            ORDER BY EntryDate DESC';

如果两个表之间包含链接，请使用INNER JOIN拉入第二个pictures表。

SELECT tbla.x, tbla.y, tblb.f
FROM tbla
    INNER JOIN tblb ON tblb.id = tbla.tblbid
WHERE tbla.x = 'test' AND tblb.f > 4;

我试着让这个例子尽可能简单。

好吧，看起来你应该从你的评论和回复中走UNION路线。

SELECT PictureCount AS PIC_COUNT, DATE(PictureModifiedDateTime) AS FILE_DATE, ListingRid AS HOME_ID 
            FROM (SELECT * FROM ' . TB_RESI . ' 
            WHERE (COUNTY = "Lee") AND (
                ListingOfficeMLSID = "PREFP" OR 
                ListingOfficeMLSID = "PREFP2" OR 
                ListingOfficeMLSID = "PREFP3" OR 
                ListingOfficeMLSID = "PREFP4" OR 
                ListingOfficeMLSID = "PREFP6"
            ) 
            AND (PictureCount >= 4) 
            ORDER BY EntryDate DESC
UNION
            SELECT PictureCount AS PIC_COUNT, DATE(PictureModifiedDateTime) AS FILE_DATE, ListingRid AS HOME_ID 
            FROM (SELECT * FROM ' . TB_RESI2 . ' 
            WHERE (COUNTY = "Lee") AND (
                ListingOfficeMLSID = "PREFP" OR 
                ListingOfficeMLSID = "PREFP2" OR 
                ListingOfficeMLSID = "PREFP3" OR 
                ListingOfficeMLSID = "PREFP4" OR 
                ListingOfficeMLSID = "PREFP6"
            ) 
            AND (PictureCount >= 4) 
            ORDER BY EntryDate DESC

其中TB_RESI是第一个表的名称，TB_RESI2是第二个表名称

使用UNION组合两个表

$sql = 'SELECT PictureCount AS PIC_COUNT, DATE(PictureModifiedDateTime) AS FILE_DATE, ListingRid AS HOME_ID 
            FROM (SELECT * FROM ' . TB_RESI . ' UNION SELECT * FROM ' . RESI . ') AS ALL' .
            ' WHERE (COUNTY = "Lee") AND (
                ListingOfficeMLSID = "PREFP" OR 
                ListingOfficeMLSID = "PREFP2" OR 
                ListingOfficeMLSID = "PREFP3" OR 
                ListingOfficeMLSID = "PREFP4" OR 
                ListingOfficeMLSID = "PREFP6"
            ) 
            AND (PictureCount >= 4) 
            ORDER BY EntryDate DESC';

如果您出于某种原因不想执行JOIN，许多风格的SQL（如MySQL）都允许您命名表，并在WHERE子句中使用该名称：

 FROM ' . TB_RESI . ' as TBR, ' . TC_RESI . ' as TCR ' .

 WHERE TBR.field1 = TCR.field2

等等。当然，您应该看看使用JOIN是否是一个更好的解决方案。