CODEWITHSUNDEEP
sqlplsql
relysis
relysis

Reputation: 1075

Columns Intersection

Suppose i have a table like

ID     PROD     UNIT
1    |  100  | X
2   |  100   |  Y
3   |  100   |  Z
4   |  200   |  X
5   |  200   |  Y
6   |  300   |  Y
7   |  300   |  Z

I want to obtain an intersection of values from UNIT column for each code from PROD column, by an SQL query. The number of products in table is variable but i need to intersect UNIT for all products. For my example, the result table would be a table with a single row, since all products from PROD have only one UNIT in common:

PROD    UNIT
300     Y

Upvotes: 0

Views: 65

Answers (1)

Matt
Matt

Reputation: 14361

SELECT MAX(PROD) as Prod, Unit
FROM
    TableName
GROUP BY
    Unit
HAVING
    COUNT(DISTINCT PROD) = (SELECT COUNT(DISTINCT PROD) FROM TableName)

You need to Be able to compare the DISTINCT Count of Products grouped by Unit with the DISTINCT Count of All Products in the table. You can use a sub query in the HAVING Clause to accomplish this.

I assumed you wanted the Maximum PROD Id, based on your outcome if you want something else of if you want all records simply use this as a derived table and join back to get your desired results.

Upvotes: 1

Related Questions