用于高级搜索的php sql查询

Question

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?

Answer 1

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.