PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – SQL: How to use grouping and condition at the same time in a table?

I have a table with such structure:

id product company
1 table A
2 table B
3 table C
4 chair A
5 chair B
6 sofa A
7 sofa C

I need to find all products whose companies include both A and B. In the example above these would be table and chair.

I managed to write a query that will return products that have more than one companies:

SELECT product FROM table_name
GROUP BY product HAVING COUNT(*) > 1
order by COUNT(*) desc;

But I can’t make a selection taking into account certain companies, and not just their number.
I would be grateful for any help!

Tags:

2

Answers


  1. 0 
      SELECT product
    FROM table_name
   WHERE company IN ('A', 'B')
GROUP BY product
  HAVING COUNT(DISTINCT company) = 2
ORDER BY COUNT(*) desc;
    Login or Signup to reply.
  2. 0

    I think, we can do it by self join on table on product column. Here is the example:

    SELECT t1.product
    from table_name t1, table_name t2
    where t1.product=t2.product
    and t1.company=’A’ and t2.company=’B’

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search