[SQL][MS access] Query to find duplicate item in 2 table
- by Rico
I have this table
Antecedent Consequent
I1 I2
I1 I1,I2,I3
I1 I4,I1,I3,I4
I1,I2 I1
I1,I2 I1,I4
I1,I2 I1,I3
I1,I4 I3,I2
I1,I2,I3 I1,I4
I1,I3,I4 I4
AS you can see it's pretty messed up.
is there anyway i can remove rows if item in consequent exist in antecedent (in 1 row)
for example:
INPUT:
Antecedent Consequent
I1 I2
I1 I1,I2,I3 <---- DELETE since I1 exist in antecedent
I1 I4,I1,I3,I4 <---- DELETE since I1 exist in antecedent
I1,I2 I1 <---- DELETE since I1 exist in antecedent
I1,I2 I1,I4 <---- DELETE since I1 exist in antecedent
I1,I2 I1,I3 <---- DELETE since I1 exist in antecedent
I1,I4 I3,I2 <---- DELETE since I2 exist in antecedent
I1,I2,I3 I1,I4
I1,I3,I4 I4 <---- DELETE since I4 exist in antecedent
OUTPUT:
Antecedent Consequent
I1 I2
I1,I2,I3 I1,I4
is there anyway i can do that by query?