Search code examples
sqlsql-serverdatabasemany-to-many

How to check if a gerund relationship is exist and valid in database

I have a scenario in which I have a table that is combination of many-to-many relationship, I want to make sure that relationship of association exists only once , no repeating records do exists in it.

I am pasting data over here, I need to make sure that there is not repeating , that violates "gerund" property.

ItemID  ProductID   ProductAssociationGroupID   
3064    10084   11  
3065    10705   11  
3066    11766   68  
3067    11766   75  
3068    11772   106 
3069    11778   11  
3070    11779   98  
3071    11779   93  
3072    11793   93  
3073    12073   20  
3074    12178   12  
3075    12561   12  
3076    12561   17  
3077    12561   82  
3078    12561   81  
3079    12561   77  
3080    12561   76  
3081    12573   37

How may I query to find out the exact relationship exists only once in the above data?

Solution

  • I am still awaiting some clarification on the columns, but I believe using the HAVING clause will give you the results you want.

    Select ProductID, ProductAssociationGroupID
from [Database]
Group by ProductAssociationGroupID, ProductID
Having (Count(ProductAssociationGroupID) = 1)

    If you want to find duplicates, then I would just change the HAVING clause to greater than.