i need to do a particular query for an advanced search.
i provide a sample of db structure
id code content id_post -------- -------- ----------- ------------- 1 reg lazio 1 2 reg lazio 2 3 type typeX 1 4 type typeY 2
now i have to do some search in this table and get right id_post, for example:
i want all id_post that have code = reg and content = typeY in this case there are no results
2d example--> all id_post with code = reg and content = lazio the result must be 1 and 2
3d example--> all id_post with (code = reg and content = lazio) and (code = type and content = typeY) the result must be 2
and so on....
how can I set the three queries?
Try this:
Example 1:
select id_post from your_table where code = 'reg' and content = 'typeY'
Example 2:
select id_post from your_table where code = 'reg' and content = 'lazio'
Example 3:
select t1.id_post from your_table as t1
inner join (select id_post from your_table where code = 'type' and content = 'typeY') as t2 on t1.id_post = t2.id_post
where t1.code = 'reg' and t1.content = 'lazio'
I have run tests to verify the results. You can verify them yourself on this sqlfiddle
Example 3 is basically an intersection, which is why neither the and and or approaches work. You can refer to this question for further ways to solve this type of query.