Data comparison in the same table in sql

Question

I have a table called productfops, I have column like entity, services and fop

Entity	services	fop
Gpl	YouTube	credit
Gpl	gpay	credit
Gpl	play	debit
Gpil	YouTube	credit
Gpil	gpay	credit

This is already existing data and if there is any new service in combination with entity and service like gpl, gsuit, credit as the combination is not already existing it should be identified, next the combination with entity, service, fop like gpl, YouTube, debit as this combination also doesn't exist it should be identified.

Need a result like

Entity | services | fop |
|:---- |:------:| -----:|
| Gpl  | YouTube | debit
| Gpl | gsuit | credit |

Please help with this

Script I used

SELECT entity, service, fop
FROM tableA
WHERE (entity, service) IN (
    SELECT entity, service
    FROM tableA
    GROUP BY entity, service
    HAVING COUNT(*) > 1
);

This didn't work as there are already existing unique columns so >1 or =1 isn't accurate

Other script i tried is

CREATE OR REPLACE TABLE b AS
(
    -- Combinations of two columns (entity and service)
    SELECT a.entity, a.service, NULL AS fop
    FROM a
    WHERE CONCAT(a.entity, ' - ', a.service) NOT IN (
        SELECT CONCAT(b.entity, ' - ', b.service)
        FROM b
    )
    UNION ALL
    -- Combinations of three columns (entity, service, and fop)
    SELECT a.entity, a.service, a.fop
    FROM a
    WHERE CONCAT(a.entity, ' - ', a.service, ' - ', a.fop) NOT IN (
        SELECT CONCAT(b.entity, ' - ', b.service, ' - ', b.fop)
        FROM b
    )
)

Even this didn't work as i am using not in on the same table

Answer 1

Your question is not very clear.

If you want to find all possible combinations of distinct entity, services and fop values that are not currently in your table you can cross join the individual distinct queries to get all combinations, and then left join to the original table to find the missing combinations. Something like this:

WITH mytable (entity, services, fop) AS (
    VALUES
        ROW('Gpl', 'YouTube', 'credit'),
        ROW('Gpl', 'gpay', 'credit'),
        ROW('Gpl', 'play', 'debit'),
        ROW('Gpil', 'YouTube', 'credit'),
        ROW('Gpil', 'gpay', 'credit')
)
SELECT *
FROM (SELECT DISTINCT entity FROM mytable) e
CROSS JOIN (SELECT DISTINCT services FROM mytable) s
CROSS JOIN (SELECT DISTINCT fop FROM mytable) f
LEFT JOIN mytable m
    ON e.entity = m.entity
    AND s.services = m.services
    AND f.fop = m.fop
WHERE m.entity IS NULL;

For the given sample data this outputs:

entity	services	fop	entity	services	fop
Gpl	play	credit
Gpil	play	credit
Gpil	play	debit
Gpl	gpay	debit
Gpil	gpay	debit
Gpl	YouTube	debit
Gpil	YouTube	debit