Find records, which have several specific records in a joined table-CodePudding

I have two tables as follows:

create table gift_certificate
(
    id               int auto_increment
        primary key,
    name             varchar(64)       not null,
    description      mediumtext        not null,
    price            decimal default 0 not null,
    duration         int     default 1 not null,
    create_date      datetime          not null,
    last_update_date datetime          not null
)

and

create table tag
(
    id   int auto_increment
        primary key,
    name varchar(64) not null,
    constraint tag_name_uindex
        unique (name)
)

with a linking table:

create table gift_certificate__tag
(
    certificate_id int not null,
    tag_id         int not null,
    primary key (certificate_id, tag_id),
    constraint gift_certificate__tag_gift_certificate_id_fk
        foreign key (certificate_id) references gift_certificate (id),
    constraint gift_certificate__tag_tag_id_fk
        foreign key (tag_id) references tag (id)
)

I need to search for gift certificates by several tags (“and” condition). I only came up with a solution for one tag

select distinct gc.*, tag.* from gift_certificate gc
    left outer join gift_certificate__tag joint on gc.id=joint.certificate_id
    left outer join tag on joint.tag_id=tag.id
    where tag.name='puppy'
    order by gc.id desc;

Would be grateful for some support

CodePudding user response：

You can aggregate the joint table by certificate and use HAVING to only keep certificates that have all the tags. Then select all matching certificates using an IN clause. For instance:

select *
from gift_certificate
where id in
(
  select joint.certificate_id 
  from gift_certificate__tag joint
  join tag on joint.tag_id=tag.id
  group by joint.certificate_id
  having max(case when tag.name = 'puppy' then 1 else 0 end) = 1
     and max(case when tag.name = 'something' then 1 else 0 end) = 1
);

As true = 1 and false = 0 in MySQL, you can shorten the expression to

  having max(tag.name = 'puppy')

if you find this readable. Or

  having sum(tag.name = 'puppy') > 0