Out of my element: SQL statment construct question

Databases
1

OK, here’s the deal. I’m way outta my league, but am trying to construct a SQL statement and here’s what I have:

$querydetails = "
 SELECT wposts.*
 FROM $wpdb->posts wposts, $wpdb->postmeta wpostmeta
 WHERE wposts.ID = wpostmeta.post_id
 AND wposts.post_status = 'publish'
 AND wposts.post_type = 'page'
 AND (
        (wpostmeta.meta_key = 'available'
         AND wpostmeta.meta_value =  'Yes'
        )
     OR
        (wpostmeta.meta_key = 'bedrooms'
        AND wpostmeta.meta_value = '2'
        ) 
    ) 
 ORDER BY wposts.post_date DESC
 ";

I am trying to extract the records that are both AVAILABLE and have TWO BEDROOMS. This statement extracts ALL the records that are available in addition to the records that have two bedrooms. In fact, for each record that is both available and has two bedrooms it extracts the record TWICE.

It seemed logical to me to change the OR to and AND and that doing so would limit the records to those meeting both criteria. However, doing so actually causes an empty query to be returned.

What I would like to achieve is a list of records that are BOTH available and have two bedrooms. Any help much appreciated.

2

Thanks so much for the solution and the very informative explanation of why it works! Much appreciated.

3

you are correct

let’s pretend that we are executing the subquery by itself, and see what happens as it processes the following sample data –

post_id  meta_key     meta_value
   9     available    Yes
   9     bedrooms     1
   9     price        200,000
  37     available    Yes
  37     bedrooms     2
  37     price        290,000
  42     available    No
  42     bedrooms     2
  42     price        300,000

first of all, the WHERE clause will filter out all the rows that are not of interest to the query

specifically, only the following rows are accepted –

post_id  meta_key     meta_value
   9     available    Yes
  37     available    Yes
  37     bedrooms     2
  42     bedrooms     2

you can see that only those rows that satisfy the WHERE clause are accepted, right?

okay, then the GROUP BY happens, and as you know, GROUP BY will perform an aggregation, which means it collapses multiple rows into one, produces one row per group, and calculates any aggregate functions for each group

in this case, the aggregate function being used is COUNT in the HAVING clause

so let’s do the GROUP BY, and we see the following –

post_id  COUNT(*)
   9     1
  37     2
  42     1

now the HAVING clause takes over, and of course it ensures that the query returns only post_id 37, because it had both rows of interest

the post_id values which satisfy the subquery are “passed to” the outer query, so that the only post rows returned by the outer query are the ones that satisfied the subquery

so yes, if you were adding another condition, you’d add those conditions to the subquery’s WHERE clause and adjust the HAVING clause

4

If you have time can you explain a bit how this works? I incorrectly think that the OR should be an AND, and do not understand this logic? Also, a quick comment on what HAVING COUNT(*) = 2 ) AS t does?

I would like to add two more fields to this query as well. Something tells me that the HAVING COUNT statement might need to be changed to the number of fields? Or am I way off base?

5

My bad. It works! I made a typo when I copied your code. Thanks very, very much for the assist. Much appreciated!

6

I’m really in uncharted territory here. I am not sure I know what you mean when you say run the t subquery by itself? Advice?

7

bummer

please run the t subquery by itself and see what that does

8

Thanks so much for the effort and suggestion, but the statement you provided me renders an empty query!

9 
SELECT wposts.*
  FROM $wpdb->posts wposts
INNER
  JOIN ( SELECT post_id
           FROM $wpdb->postmeta wpostmeta
          WHERE ( ( wpostmeta.meta_key = 'available'
                AND wpostmeta.meta_value =  'Yes' )
               OR ( wpostmeta.meta_key = 'bedrooms'
                AND wpostmeta.meta_value = '2' ) 
                ) 
         GROUP
             BY post_id
         HAVING COUNT(*) = 2 ) AS t  
    ON t.post_id = wposts.ID
 WHERE wposts.post_status = 'publish'
   AND wposts.post_type = 'page'
ORDER 
    BY wposts.post_date DESC
10

@r937: Would the result of running this query be an array with only the post_id as the content? One row, no columns?

11

first of all, if there are no column, you can’t actually have a row :slight_smile:

but more importantly, which query are you talking about?