CODEWITHSUNDEEP
mysqlsqlwordpress
Chester Profugo
Chester Profugo

Reputation: 33

SQL - Multiple Where in one column

Sorry Guys I was using a different example, instead of using COuntry table from w3school I updated my question and give you the real situation.

Im using wordpress and I want to filter out all posts with multiple categories.

`SELECT DISTINCT p.id,p.post_title,p.post_content,t.name,tax.taxonomy from 
 wp_posts as p 
 LEFT JOIN wp_term_relationships rel ON rel.object_id = p.ID 
 LEFT JOIN wp_term_taxonomy tax ON tax.term_taxonomy_id = 
 rel.term_taxonomy_id 
 LEFT JOIN wp_terms t ON t.term_id = tax.term_id 
 WHERE 1=1 
 AND p.post_status = 'publish' 
 AND p.post_title LIKE '%lorem%' 
 OR p.post_content LIKE '%lorem%' `

I want to use raw sql because I know that conditions will be longer than that.

Here is what Iam getting https://d.pr/free/i/mrNHXk

But when I added this

`AND t.name = 'CGN'
AND t.name = 'Harmony'`

I got no result, iam expecting this https://d.pr/free/i/0NER1F

Using 

AND t.name IN ('CGN','Harmony')

Will not work because The result must have both 'cgn' and 'harmony', if I added 'cfw' on the condition like

AND t.name IN ('CGN','Harmony','cfw')

The result post should have those 3 categories

Upvotes: 1

Views: 89

Answers (3)

NineBerry
NineBerry

Reputation: 28499

Do not join directly with the taxonomy tables, but use a subquery and the exists predicate to check whether the tags Harmony and CGN exist for the post.

select p.id, p.post_title, p.post_content,
    (select GROUP_CONCAT(t.name) from wp_term_relationships rel 
        inner join wp_term_taxonomy tax ON tax.term_taxonomy_id = rel.term_taxonomy_id
        inner join wp_terms t ON t.term_id = tax.term_id 
        where rel.object_id = p.ID) as terms
from wp_posts as p 
where
  p.post_status = 'publish' 
  and (p.post_title LIKE '%lorem%' OR p.post_content LIKE '%lorem%' )
  and exists (select * from wp_term_relationships rel 
      inner join wp_term_taxonomy tax ON tax.term_taxonomy_id = rel.term_taxonomy_id
      inner join wp_terms t ON t.term_id = tax.term_id
      where  rel.object_id = p.ID and t.name = 'Harmony')
  and exists (select * from wp_term_relationships rel 
      inner join wp_term_taxonomy tax ON tax.term_taxonomy_id = rel.term_taxonomy_id
      inner join wp_terms t ON t.term_id = tax.term_id
      where  rel.object_id = p.ID and t.name = 'CGN')

Upvotes: 2

Pablo Ocaña
Pablo Ocaña

Reputation: 57

you will not get any results of that query. The reason is that the Country value can't be both 'Germany' and 'USA' at the same time (in a logical/propositional way).

Try:

SELECT * FROM Customers WHERE Country in ('Germany','USA')

other possibility:

SELECT * FROM Customers WHERE Country like '%Germany%' or like '%USA%'

Upvotes: 2

Code of Arms
Code of Arms

Reputation: 11

As stated above, the case where country is both Germany and USA shouldn't exist. It would seem you are looking for:

SELECT * FROM Customers
WHERE Country IN ('Germany', 'USA');

Using the IN clause with a list will return all rows in the customer table where the country contains either Germany or USA.

https://www.w3schools.com/sql/trysql.asp?filename=trysql_select_in2

Upvotes: 1

Related Questions