CODEWITHSUNDEEP
phpmysql
Rocco The Taco
Rocco The Taco

Reputation: 3777

Use of AND instead of JOIN with PHP based MySQL query

From what I can tell, AND cannot be used to specify two different tables, but is there another way to simply query the exact same column names in two different tables?

Currently I have a PHP variable:

define('TB_RESI', 'RESI');

And a query like below. I need to also query a table named RESI with the same columns...what is the best way to achieve this?

Current Query:

$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';

Upvotes: 0

Views: 55

Answers (3)

Ian Davis
Ian Davis

Reputation: 109

If the two tables contain a link between them use INNER JOIN to pull in the second pictures table.

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;

I have tried to keep the example as simple as possible.

Ok, it seems like you should go down the UNION route from the comments and replies you have made.

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

Where TB_RESI is the name of your first table and TB_RESI2 is the second table name

Upvotes: 1

Phil Perry
Phil Perry

Reputation: 2130

If you don't want to do a JOIN for some reason, many flavors of SQL (such as MySQL) allow you to name tables, and use that name in the WHERE clause:

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

...

 WHERE TBR.field1 = TCR.field2

etc. Of course, you should be looking to see if using JOIN would be a better solution.

Upvotes: 0

niyou
niyou

Reputation: 873

Use UNION to combine both tables

$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';

Upvotes: 1

Related Questions