> On 12 Sep 2018, at 17:44, Arup Rakshit <ar@xxxxxxx> wrote: > > Can you show me the SQL construction? Do I need to use `WITH`? An option is to create a bit-wise OR and SUM the grouped results. If the result of these 3 bits is 7, than the post matches all three tags. select p.id, p.name from post p join post_tag pt on (pt.post = p.id) join tag t on (t.id = pt.tag) where t.id in (1, 2, 3) group by case t.id when 1 then 1 when 2 then 2 when 3 then 4 else 0 end having sum(case t.id when 1 then 1 when 2 then 2 when 3 then 4 else 0 end) = 7; I used ints here for the bitwise OR, a bitstring would probably be neater. Another approach is to aggregate the set of matching tags into an array using array_agg(). I think that's what David means. You could then check the length of the array to see if you have all 3 (or 4 or 5 or 9000). >> On 12-Sep-2018, at 9:13 PM, David G. Johnston <david.g.johnston@xxxxxxxxx> wrote: >> >> On Wednesday, September 12, 2018, Arup Rakshit <ar@xxxxxxx> wrote: >> IN is OR, I want the AND logic. Select posts which has tag 1, 2 and 3 ( tag ids ) >> >> Build arrays and then use the “contains” operator. >> >> David J. > Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll find there is no forest.
