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 🙂
3
Answers
Simply look at each group of post_data rows and check for your values with an aggregate function, in the having clause after grouping.
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 withwhere
) 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.You can check with HAVING if you hevnumber of values is 2