1

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?

user3235728
  • 13
  • 4
  • And what is the problem exactly? – Klaus Byskov Pedersen Feb 03 '14 at 20:27
  • the structure of the query – user3235728 Feb 03 '14 at 20:29
  • **Try writing something yourself** and then if it doesn't work, show us specifically what you did so we can help you along. You start it, we help. We don't write it for you. Show us the actual code that you've tried and then we can help you from there. Chances are you'll get pretty close to the answer if you just try it yourself first. – Andy Lester Feb 03 '14 at 21:37

1 Answers1

0

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.

Community
  • 1
  • 1
Klaus Byskov Pedersen
  • 117,245
  • 29
  • 183
  • 222