select columns’ values based on another two values from another column

I have this data set and I need to extract multiple columns values based on on column value.

To elaborate on it, I have a rental table, inventory table, customer table, and I want to relate customer table to rental table by customer_id and relate rental table to inventory table by inventory_id, the final purpose is to generate first_name and last_name of customers of specified customer_id.

by

SELECT * 
FROM (
     SELECT 
     inventory.inventory_id,inventory.store_id,
     rental.customer_id,customer.first_name,customer.last_name
     FROM inventory
     INNER JOIN rental ON inventory.inventory_id=rental.inventory_id
     INNER JOIN customer ON rental.customer_id=customer.customer_id
     WHERE inventory.film_id='25') AS data
;

I have achieved the result of

inventory_id | store_id | customer_id | first_name | last_name 
--------------+----------+-------------+------------+-----------
          124 |        1 |         265 | Jennie     | Terry
          124 |        1 |         497 | Gilbert    | Sledge
          124 |        1 |         158 | Veronica   | Stone
          125 |        1 |         377 | Howard     | Fortner
          125 |        1 |         163 | Cathy      | Spencer
          125 |        1 |         141 | Debbie     | Reyes
          125 |        1 |         558 | Jimmie     | Eggleston
          126 |        1 |         327 | Larry      | Thrasher
          126 |        1 |         147 | Joanne     | Robertson
          126 |        1 |         225 | Arlene     | Harvey
          126 |        1 |         393 | Philip     | Causey
          126 |        1 |         425 | Francis    | Sikes
          127 |        1 |         336 | Joshua     | Mark
          127 |        1 |         350 | Juan       | Fraley
          128 |        2 |          96 | Diana      | Alexander
          128 |        2 |         273 | Priscilla  | Lowe
          128 |        2 |         163 | Cathy      | Spencer
          129 |        2 |         498 | Gene       | Sanborn
          129 |        2 |         360 | Ralph      | Madrigal
          129 |        2 |         292 | Misty      | Lambert
          129 |        2 |         578 | Willard    | Lumpkin
          129 |        2 |          53 | Heather    | Morris

However I want to take a step further to obtain the following table

 inventory_id | store_id | customer_id | first_name | last_name 
--------------+----------+-------------+------------+-----------
          124 |        1 |         265 | Jennie     | Terry
          126 |        2 |         265 | Jennie     | Terry
          125 |        1 |         163 | Cathy      | Spencer
          128 |        2 |         163 | Cathy      | Spencer

Which is to obtain column values customer_id,first_name and last_name by checking the occurrence of customer_id for both store_id='1' and store_id='2'. I know that I need to do COUNT (DISTINCT customer_id) but I cannot figure out how to apply to achieve it. Any help will be very much appreciated!

Answer

Are you looking for customers of both stores? One method uses exists:

with t as (
      SELECT i.inventory_id, i.store_id,
             r.customer_id, c.first_name, c.last_name
      FROM inventory i JOIN
           rental r
           ON i.inventory_id = r.inventory_id JOIN
           customer c
           ON r.customer_id = c.customer_id
      WHERE i.film_id = 25
     )
select t.*
from t
where t.store_id in ( 1, 2) and
      exists (select 1
              from t t2
              where t2.customer_id = t.customer_id and
                    t2.store_id in (1, 2) and
                    t2.store_id <> t.store_id
             );

However, if you just want the customers who have purchased in both stores, you can use the simpler query:

SELECT r.customer_id, c.first_name, c.last_name
FROM inventory i JOIN
     rental r
     ON i.inventory_id = r.inventory_id JOIN
     customer c
     ON r.customer_id = c.customer_id
WHERE i.film_id = 25 AND
      r.store_id in (1, 2)
GROUP BY r.customer_id, c.first_name, c.last_name
HAVING COUNT(DISTINCT r.store_id) = 2;