Mysql Question posted in
The official MySQL documentation can be found here.

Mysql Query For Multiple Categories

I have two tables: posts, and post_data, posts has an id, and post_data is linked to posts with the column post_id, here is the structure:

Posts
id 
name

Post_Data
id
post_id
Value

Okay, so now I have some entries in the post table and some entries in the post_data table
The post_data table entries look like this:

id: 1
post_id:1001
value: test

id:2
post_id:1001
value: hello

id:3
post_id:1002
value: test

I need help writing a query that will ONLY return posts that have post_data of both "hello" and "test". I know how to write the query to reutrn any post with post_data of "hello" OR "test" but I can’t seem to figure out how to write a query that only returns posts with BOTH "hello" and "test"

Thanks in advance 🙂

Tags:

3

Answers


  1. 0

    Simply look at each group of post_data rows and check for your values with an aggregate function, in the having clause after grouping.

    select post_id
from post_data
group by 1
having sum(value="hello")
    and sum(value="test")
    Login or Signup to reply.
  2. 0

    you can do 2 joins in order to place the 2 conditions in the where

    SELECT distinct(p.id) FROM posts as p left join post_data as pd1 on p.id = pd1.post_id left join post_data as pd2 on pd1.post_id = pd2.post_id WHERE pd1.value = "hello" and pd2.value = "test"

    That will do the work.

    Note: I’d be a bit concerned about performance since depending on your DB this will create a really big set of data (joins build sets of data that then we can filter with where) I know this was only an example so I won’t dig to much on your example, but the way to improve this would be revisit the foundation and design of your schema.

    Login or Signup to reply.
  3. 0

    You can check with HAVING if you hevnumber of values is 2

    SELECT post_id 
from Pist_data 
WHERE value in ('hello','test') 
GROUP BY post_id 
HAVING COUNT(*) =2
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search