where on multiple join postgresql



  • I have problem when query using postgresql I have several tables:

    numbers:

    id phone
    1 08211111
    2 08211112

    domains:

    id name is_blacklisted
    1 abc.com 0
    2 123.com 0
    3 456.com 1

    domain_groups:

    id name
    1 alphabet
    2 numeric

    domain_group_domains:

    domain_group_id domain_id
    1 1
    2 2
    2 3

    number_domain_history:

    number_id domain_id times
    1 1 1
    1 2 2
    3 3 2

    number_domain_group_history:

    number_id domain_group_id times
    1 1 1
    1 2 2
    3 2 2

    if I want to select number by domain history where domain.is_blacklisted = 0:

    select numbers.id from numbers
    left join number_domain_history on number_domain_history.number_id = numbers.id
    where number_domain_history.domain_id not in (select domain_id from domains where is_blacklisted = 1)
    group by numbers.id
    limit 1000
    

    The problem is how I can search number by domain_group that perform same blacklist since blacklisted only available in number_domain_history?

    I have try with:

    select numbers.id from numbers
    left join number_domain_history on number_domain_history.number_id = numbers.id
    left join number_domain_group_history on number_domain_group_history.
    where number_domain_history.domain_id not in (select domain_id from domains where is_blacklisted = 1)
    group by numbers.id
    limit 1000
    

    But the result is not like what I expect to return, I still get data from domain_group that the domain already blacklisted.

    Is there any workaround?



  • if I want to select number by domain history where domain.is_blacklisted = 0:

    You only need three tables:

    1. numbers
    2. domains
    3. number_domain_history

    You can use a simple inner join:

    select n.phone
    from numbers n 
    inner join number_domain_history ndh on ndh.number_id=n.id
    inner join domains d on ndh.domain_id=d.id
    where d.is_blacklisted=0
    group by n.phone;
    

    Result:

    phone
    08211111
    

    https://dbfiddle.uk/?rdbms=postgres_13&fiddle=c2234951f98096889aa87ae340600667

    How can I perform search by times on number_domain_group_history ? ex: number_domains_group_history.times > 0

    Simple , add another join condition.

    inner join number_domain_group_history ndgh on ndgh.number_id=n.id
    

    select n.phone
    from numbers n
    inner join number_domain_history ndh on ndh.number_id=n.id
    inner join domains d on ndh.domain_id=d.id
    inner join number_domain_group_history ndgh on ndgh.number_id=n.id
    where d.is_blacklisted=0
    and ndgh.times > 0
    group by n.phone;

    https://dbfiddle.uk/?rdbms=postgres_13&fiddle=1f71f4977998a8deaec201aafd6ac9a5


Log in to reply
 


Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2