user1063192 user1063192 - 3 months ago 9
MySQL Question

MySQL Query: Getting COUNT of one table where the ID of table 1 has a value of X in a different table

I am working with Wordpress creating a filter and am struggling to figure out this raw query.

This is what I have now:

SELECT COUNT(*) FROM active_listings
WHERE listing_type = "sales"
AND price > 50000
AND beds >= 3
AND baths >= 4
AND active = "1"
AND (dstatus IS NULL OR (dstatus != "Temporarily No Showings" AND dstatus != "Contingent"))
AND location in (11)


But I also need to check the
wp_postmeta
table to see if a row that has a matching
post_id
has the
meta_key
of
wpcf-active
and the
meta_value
of
1
.

Answer

Just use an INNER JOIN. This will select the records from both tables that have the same post_id. You might have to adjust the column names, etc. but it will look roughly like this:

SELECT COUNT(*)
FROM active_listings INNER JOIN wp_postmeta
ON active_listings.post_id = wp_postmeta.post_id
WHERE listing_type = "sales" 
AND price > 50000 
AND beds >= 3 
AND baths >= 4 
AND active = "1" 
AND (dstatus IS NULL OR (dstatus != "Temporarily No Showings" AND dstatus != "Contingent")) 
AND location in (11)
AND meta_key = 'wpcf-active' 
AND meta_value = 1
Comments