Joining customers and tags table

问题:

I have these tables

    Customers
    CustomerNumber    date
    001               8/1/2017
    002               8/2/2017
    003               8/3/2017

    Tags
    Index   Tag   Description 
    1       NEW    New customer
    2       OTHER  Some other tag

   Customers_Tags
   TagIndex    CustomerNumber
   1           001
   1           002
   2           002
   2           003

How can I, in a single query, get all customers with tag 1 and also any other tags those customers have? So if looking for tag 1 get:

customer    tag    date
001         1      8/1/2017
002         1      8/2/2017
002         2      8/2/2017

回答1:

using exists() to get all customers and tags when that customer has a tag of index 1:

select ct.customernumber, ct.tagindex, c.date
from customers c
  inner join customers_tags ct
    on c.customernumber = ct.customernumber
where exists (
  select 1
  from customers_tags i
  where i.customernumber = ct.customernumber
    and i.tagindex = 1
  )

---

or using in():

select ct.customernumber, ct.tagindex, c.date
from customers c
  inner join customers_tags ct
    on c.customernumber = ct.customernumber
where c.customernumber in  (
  select i.customernumber
  from customers_tags i
  where i.tagindex = 1
  )