[SOLVED] Advanced SQL help

[stevebluck]

Check out this query, you only need to worry about the last 3 sections:

 

[pre]SELECT the_posts.*

FROM wp_posts the_posts, wp_postmeta pm

WHERE the_posts.id = pm.post_id AND

 

the_posts.id IN (

SELECT xposts.id

FROM wp_posts xposts, wp_term_relationships catspost

WHERE catspost.object_id = xposts.id

AND catspost.term_taxonomy_id IN (1)

 

) AND (

 

(pm.meta_key = 'state_value'

AND pm.meta_value = 'Suffolk')

 

OR

 

(pm.meta_key = 'price_value'

AND pm.meta_value >= '100000')

 

OR

 

(pm.meta_key = 'beds_value'

AND pm.meta_value = '2')

)

GROUP BY the_posts.id;

[/pre]

 

This works fine, but instead of saying "OR" I want to use "AND", but when I replace the word "OR" with "AND" it doesn't want to work. Basically I want to say:

 

If the state_value is equal to Suffolk AND the price_value is greater than or equal to 100000 AND the beds_value is equal to 2.

 

instead of saying

 

If the state_value is equal to Suffolk ORif the price_value is greater than or equal to 100000 ORif the beds_value is equal to 2.

 

Help would be greatly appreciated!

 

[cags]

I'm not sure it would make any difference, but when changing the OR to an AND did you remove the set of brackets that would no longer be required.

 

Making it...

(pm.meta_key = 'state_value' AND pm.meta_value = 'Suffolk' AND pm.meta_key = 'price_value' AND pm.meta_value >= '100000')

 

Rather than...

(pm.meta_key = 'state_value' AND pm.meta_value = 'Suffolk') OR (pm.meta_key = 'price_value' AND pm.meta_value >= '100000')

[Mchl]

pm.meta_value will never be higher than 100000 and equal to two and equal to 'Suffolk' at once

[stevebluck]

pm.meta_value will never be higher than 100000 and equal to two and equal to 'Suffolk' at once

 

The pm.meta_value is assosiated the the pm.meta_key.

 

So it is saying:

 

The meta key "state_value" has to have "Suffolk" as the value OR

The meta key "price_value" has to have "1000000" or higher as the value OR

The meta key "beds_value" has to have "2" as the value

 

I removed the quotes but it didnt work. I'm not getting a syntax error, it's just not giving me the results I'm expecting.

 

[stevebluck]

Lets start again

 

[pre]SELECT the_posts.*

FROM wp_posts the_posts, wp_postmeta pm

WHERE the_posts.id = pm.post_id AND

 

the_posts.id IN (

SELECT xposts.id

FROM wp_posts xposts, wp_term_relationships catspost

WHERE catspost.object_id = xposts.id

AND catspost.term_taxonomy_id IN (1)

 

)AND

((

  pm.meta_key = 'state_value'

AND pm.meta_value = 'Suffolk'

) OR

(

  pm.meta_key = 'price_value'

AND pm.meta_value >= '100000'

) OR

(

pm.meta_key = 'beds_value'

AND pm.meta_value = '2'

))

GROUP BY the_posts.id[/pre]

 

This query works.

 

It shows all entrys that are in suffolk OR have 2 beds OR have 100,000 or more for the price.

 

How would you go about editing the query so that it displays entrys that have 2 beds as well as being in Suffolk and as well as having 100,000 or more for the price?

 

I thought simply changing OR to AND would work. But it simply does not! I just can't seem to think what I'm doing wrong here.

[cags]

Feel free to tell me to shut up if I'm confusing matters, since as I say MySQL isn't my strongest point, but from my understanding what Mchl is saying, is that if all the ORs are swapped to ANDs, then your code would essentially be...

 

SELECT the_posts.*
FROM wp_posts the_posts, wp_postmeta pm            
WHERE the_posts.id = pm.post_id 
AND the_posts.id IN (SELECT xposts.id FROM wp_posts xposts, wp_term_relationships catspost WHERE catspost.object_id = xposts.id AND catspost.term_taxonomy_id IN (1))
AND pm.meta_key = 'state_value' 
AND pm.meta_value = 'Suffolk'
AND pm.meta_key = 'price_value'
AND pm.meta_value >= '100000'
AND pm.meta_key = 'beds_value'
AND pm.meta_value = '2'
GROUP BY the_posts.id;

 

And as you can see your are requiring pm.meta_key to be equal to 3 different values. With the OR the code made sense as you were checking if the key was one thing then the value is another, but it's impossible for the key and value to both equal 3 things.

 

EDIT: Ok you just posted again, I'll try reading that next.

[stevebluck]

Feel free to tell me to shut up if I'm confusing matters, since as I say MySQL isn't my strongest point, but from my understanding what Mchl is saying, is that if all the ORs are swapped to ANDs, then your code would essentially be...

 

Thanks cags, I know what you're saying. I think this section needs to be re-coded!

 

Any ideas?

 

So I have 2 columns pm.meta_key and pm.meta_value

 

I need to select the following from the pm.meta_key, "state_value", "price_value", "beds_value".

I need to then select the  "2" from beds_value and then "100000" or more for price_value and then "Suffolk" for state_value

 

This all needs to happen after this part:

[pre]SELECT the_posts.*

FROM wp_posts the_posts, wp_postmeta pm

WHERE the_posts.id = pm.post_id AND

 

the_posts.id IN (

SELECT xposts.id

FROM wp_posts xposts, wp_term_relationships catspost

WHERE catspost.object_id = xposts.id

AND catspost.term_taxonomy_id IN (1)

 

)[/pre]

[cags]

It sounds to me like the code you are attempting to achieve is essentially what you had in the first place...

 

(pm.meta_key = 'state_value' AND pm.meta_value = 'Suffolk')
OR
(pm.meta_key = 'price_value' AND pm.meta_value >= '100000')
OR
(pm.meta_key = 'beds_value' AND pm.meta_value = '2')

 

Was there a reason that wasn't working or something?

[stevebluck]

I only want to select entrys that have a state as "Suffolk" AND beds as "2" and price as "100,000" or more.

 

If I use the code you have displayed it would give results like this:

 

[pre]

State    | Beds | Price

-----------------------------

Suffolk  | 4 | 20000  // Becuase it has "Suffolk"

Cambridge | 2 | 500000 // Becuase it has "2"

Bury      | 0 | 150000 // Because it has more than "100000"

[/pre]

 

But I want these results:

 

[pre]

State    | Beds | Price

-----------------------------

Suffolk  | 2 | 200000

Suffolk  | 2 | 100000

Suffolk  | 2 | 150000

[/pre]

 

Get what I mean?

[cags]

Oh yes, of course, d'uh, sorry. I think I'm going to have to leave this to somebody that actually knows what they are talking about

 

Whether your whole approach is just completely wrong or I'm just not understanding I'm not sure. This is my understanding of what you have. A table called pm with the column of meta_key and meta_value. Judging by what you have described (rather than the code) I'm assuming there must be a unifying column so that you can group rows in to a meaningful set of data e.g.

 

meta_key | meta_value | foreign_key
------------------------------------
state       | Suffulk       | 2
beds        | 2              | 2
price        | 100,000     | 2
state       | Rutland      | 1
beds        | 3              | 1
price        | 450,000     | 1

 

You then assumably have another table that has a primary key that matches the foreign key. And your objective is to fetch all items from that table that matches all the meta_key/meta_value pairs. That being the case, I'd have personallythought what you needed was a JOIN of some kind (whether it be a plain JOIN or LEFT, RIGHT, INNER, OUTER, or whatever other type there is I'm not sure).

 

That all being said, it's entirely possible I've missed the point. I'm just bored and intruiged, a dangerous combination

 

[stevebluck]

Nearly there cags!

 

This is what my table looks like, it's called wp_postmeta.

 

[pre]

|  meta_id  |  post_id  |  meta_key  |  meta_value

      1          1      state_value    Suffolk

      2          1      beds_value        2

      3          1      price_value    200000

      4          2      state_value      Bury

      5          2      price_value    150000

      6          2      beds_value        5

[/pre]

 

 

And yes I have another table called wp_posts, which has the post_id's in. But this query has managed to select both tables without joining anything. How it does it I'll never know - I just want my query to work as it should

 

 

[cags]

My approach would be along the lines of...

 

SELECT `wp_posts`.* FROM `wp_posts` 
JOIN `wp_postmeta` ON `wp_postmeta`.`post_id`=`wp_posts`.`post_id`
WHERE (pm.meta_key = 'state_value' AND pm.meta_value = 'Suffolk')
OR (pm.meta_key = 'price_value' AND pm.meta_value >= '100000')
OR (pm.meta_key = 'beds_value' AND pm.meta_value = '2')

[stevebluck]

That would still give me the incorrect results becasue you're using OR. It needs to be AND I think.

 

With the query you presented I get these results:

 

[pre]

 

Suffolk  | 4 | 20000  // Becuase it has "Suffolk"

Cambridge | 2 | 500000 // Becuase it has "2"

Bury      | 0 | 150000 // Because it has more than "100000"[/pre]

 

But I need these:

 

[pre]Suffolk  | 2 | 200000

Suffolk  | 2 | 100000

Suffolk  | 2 | 150000 [/pre]

 

Thanks for trying though.

[cags]

Ooops, sorry, your right, I obviously forgot what I was doing half way through, lol. Let me try that again, I've never used the IN command, but assuming I'm grasping it correctly how does this sound?

 

SELECT * FROM `wp_posts` 
WHERE `post_id` IN (SELECT `post_id` FROM `wp_postmeta` WHERE `meta_key`='state_value' AND `meta_value`='Suffolk')
AND `post_id` IN (SELECT `post_id` FROM `wp_postmeta` WHERE `meta_key`='price_value' AND `meta_value`>='100000')
AND `post_id` IN (SELECT `post_id` FROM `wp_postmeta` WHERE  `meta_key`='beds_value' AND `meta_value`='2')

 

Not sure if it will work, even if it will there may be a better way. I would have tested it, but without your table structure it's a bit difficult so I just through I'd throw it out as a suggestion.

[stevebluck]

Ooops, sorry, your right, I obviously forgot what I was doing half way through, lol. Let me try that again, I've never used the IN command, but assuming I'm grasping it correctly how does this sound?

 

SELECT * FROM `wp_posts` 
WHERE `post_id` IN (SELECT `post_id` FROM `wp_postmeta` WHERE `meta_key`='state_value' AND `meta_value`='Suffolk')
AND `post_id` IN (SELECT `post_id` FROM `wp_postmeta` WHERE `meta_key`='price_value' AND `meta_value`>='100000')
AND `post_id` IN (SELECT `post_id` FROM `wp_postmeta` WHERE  `meta_key`='beds_value' AND `meta_value`='2')

 

Not sure if it will work, even if it will there may be a better way. I would have tested it, but without your table structure it's a bit difficult so I just through I'd throw it out as a suggestion.

 

Yeah tried this mate but unfortunately it retuns with no results, any other ideas or is there anyone else about that can give a hand?

[cags]

You are certain that there is a valid result for those search queries?

[Mchl]

You'll need to join the wp_posts table to itself three times.

 

Something like this

SELECT * FROM wp_posts AS p1
CROSS JOIN
wp_posts AS p2
USING (post_id)
CROSS JOIN
wp_posts AS p3
USING (post_id)
WHERE
p1.meta_key='state_value' AND p1.meta_value='Suffolk'
AND
p2.meta_key='price_value' AND p2.meta_value>='100000'
AND
p3.meta_key='beds_value' AND p3.meta_value='2'

 

I'm writing it off my head, so it might not work stright away, but that's the general idea.

 

[cags]

I believe meta_key and meta_value are in the table wp_postmeta. Forgive me if I'm wrong but does your code not assume it's in wp_posts?

 

I'm quite surprised stevebluck said my previous attempt didn't work, I tested it on a mock up of what I believe his table structure to be.

[Mchl]

It didn't, as the logical conjunction is associative, so whether you put parenheses around it or not, doesn't matter.

http://en.wikipedia.org/wiki/Boolean_logic

 

Anyway, as you said I mixed up the tables. It should be more like this

 

SELECT p.* 
FROM wp_posts AS p
INNER JOIN wp_postmeta AS p1
ON (p.ID = p1.post_id)
CROSS JOIN
wp_postmeta AS p2
USING (post_id)
CROSS JOIN
wp_postmeta AS p3
USING (post_id)
WHERE
p1.meta_key='state_value' AND p1.meta_value='Suffolk'
AND
p2.meta_key='price_value' AND p2.meta_value>='100000'
AND
p3.meta_key='beds_value' AND p3.meta_value='2'

[cags]

I'm sorry but in what way does what your saying about boolean logic apply to my previous suggestion? The only parentheses used are to surround the inbedded SELECT statements.

 

SELECT * FROM `wp_posts`
WHERE `post_id` IN (SELECT `post_id` FROM `wp_postmeta` WHERE `meta_key`='state_value' AND `meta_value`='Suffolk')
AND `post_id` IN (SELECT `post_id` FROM `wp_postmeta` WHERE `meta_key`='price_value' AND `meta_value`>='100000')
AND `post_id` IN (SELECT `post_id` FROM `wp_postmeta` WHERE  `meta_key`='beds_value' AND `meta_value`='2')

 

If you (or anyone) say this doesn't return the correct results, I'm perfectly willing to accept that may be true. But stevebluck says it returned no results, all I'm saying is that surprised me since I created dummy tables with his example data and it successfully returned that row and no others. Granted the JOIN method is probably better, I don't know, I didn't realise you could join the same table multiple times hence the fact I gave up on the approach earlier.

[Mchl]

Sorry... must have looked at wrong post. My bad

 

Your solution looks fine to me. It'll probably be slower than joins, but still it should work.

 

 

[stevebluck]

It didn't, as the logical conjunction is associative, so whether you put parenheses around it or not, doesn't matter.

http://en.wikipedia.org/wiki/Boolean_logic

 

Anyway, as you said I mixed up the tables. It should be more like this

 

SELECT p.* 
FROM wp_posts AS p
INNER JOIN wp_postmeta AS p1
ON (p.ID = p1.post_id)
CROSS JOIN
wp_postmeta AS p2
USING (post_id)
CROSS JOIN
wp_postmeta AS p3
USING (post_id)
WHERE
p1.meta_key='state_value' AND p1.meta_value='Suffolk'
AND
p2.meta_key='price_value' AND p2.meta_value>='100000'
AND
p3.meta_key='beds_value' AND p3.meta_value='2'

 

Fantastic, this works!

 

Can you please explain to me what you have done here? It's just so I can understand it

[Mchl]

Look up SQL joins in Wikipedia to get some insight on what they are.

 

In simple terms we can say that it takes three copies of wp_postmeta table and puts them next to each other so that their post_ids match. Then it looks for a row that has 'stat_value','Suffolk' in first copy, 'price_value','100000' in second copy and 'beds_value','2' in third copy. It also puts wp_posts next to these tables so that its ID matches post_id.

 

 

[fenway]

I really need to post a tutorial on joins so "wikipedia" isn't the answer to everything -- it's rarely helpful.

 

Basically, a field can only have a single value at any one time... if you'd like to see where it has one of N values, you'll need to join N times, simple as that (or some equivalent thereof).

[Mchl]

I don't know... http://en.wikipedia.org/wiki/Join_%28SQL%29 has some nice general examples. It's not MySQL specific for sure, but I think it does its job.