how can I get the row of value that also occurred in other row?

Question

As a beginner to sql, I have a table like below in PostgreSQL.

id  product type  occured_at                  
 1        A    6  2017-03-17 10:21:22.935278
 1        B    6  2017-04-17 10:21:22.941801
 1        B    8  2017-04-17 10:21:22.935278 
 1        B    8  2017-05-17 10:21:22.935278
 1        D    8  2017-06-17 10:21:22.935278
 2        C    4  2017-04-24 10:21:22.938517   
 3        A    8  2017-04-27 10:21:22.941801  
 4        C    8  2017-09-17 10:21:22.941801  
 4        C    6  2017-09-17 10:21:22.941801
 5        D    8  2017-09-17 10:21:22.941801

The question is how can I get the id and product which has different types that occurred on the same date. Just like

id  product     
 1        B
 4        C

If anyone can help me out, I really appreciate it :)

Answer 1

A self join is one approach:

SELECT DISTINCT A.ID, A.Product
FROM YourTable A
JOIN YourTable B ON A.ID = B.ID
                AND A.Product = B.Product
                AND DATE(A.Occured_at) = DATE(B.Occured_at)
                AND A.Type <> B.Type

Answer 2

 SELECT product  
 FROM YourTable
 GROUP BY product
          date_trunc('dat', occured_at)
 HAVING count(DISTINCT type) > 1