I have two tables, (a,b) and (b,c) where b is a FK/PK relationship. I need to find all entries in (a,b) join (b,c) on b where a and c are the same, but b differs. How would I do this? (table 2 has a couple other values, so there's different types of c... someone has created two entries in the first table linking the same a to different variations of the same c)
How would I prevent this from happening in the future? Is it possible to set a constraint where all sets of a and c must be distinct?
Madpoet on
0
Posts
admanbunionize your workplaceSeattle, WARegistered Userregular
My SQL is eternally fuzzy, but wouldn't you want to join on a=c and then check the values of a.b and c.b?
This is entrenched in a 6 year old program, so I can't do anything to the schema.
Table (a,b) is a relation between another table and table (b,c,d). So, every a (the id from table not appearing in this question) is associated with multiple entries of (b,c,d), but should only be associated with any value of c once. But I have entries that look like this:
Table a: (id1,1), (id1,2) (id1,3) table b: (1,Helmet,Red), (2,Helmet,Green),(3,Armor,Purple)
It's okay to have both purple armor and a red helmet, but not both red and green helmets. I need to detect that (1,1) and (1,2) are a conflict, and prevent it from happening again.
I'm half assing examples... here's codeage:
CREATE TABLE Adventurer(id int, name varchar(10));
CREATE TABLE A_to_G(adv_id int, gear_id int);
CREATE TABLE Gear(id int, slot varchar(10), color varchar(10));
INSERT INTO Adventurer VALUES(1,'MadPoet');
INSERT INTO Gear VALUES(1,'Helmet','Red');
INSERT INTO Gear VALUES(2,'Helmet','Green');
INSERT INTO Gear VALUES(3,'Armor','Pink');
INSERT INTO A_to_G VALUES(1,1);
INSERT INTO A_to_G VALUES(1,2); --I want to detect/prevent this
INSERT INTO A_to_G VALUES(1,3); --This is fine
Okay, that's different than the abstract descriptions yeah.
You have a many-to-many relationship and the bad records are fine relationship integrity wise. You cannot create a constraint because the constraint is not based on any one table, and you can't change the schema to make it work. (If you added the slot column to the A_to_G table, you could create a unique index on (adv_id, slot) and that would prevent insertions like you want.)
It's not hard to find the trouble records at least. Not tested but something like this:
SELECT adv_id, slot, COUNT(gear_id)
FROM A_to_G
JOIN Gear ON (A_to_G.gear_id = Gear.id)
GROUP BY adv_id, slot
HAVING COUNT(gear_id) > 1
That should show you the adventurer ID and slot of any multiples in that slot.
Posts
Care to be more specific?
Table (a,b) is a relation between another table and table (b,c,d). So, every a (the id from table not appearing in this question) is associated with multiple entries of (b,c,d), but should only be associated with any value of c once. But I have entries that look like this:
Table a: (id1,1), (id1,2) (id1,3) table b: (1,Helmet,Red), (2,Helmet,Green),(3,Armor,Purple)
It's okay to have both purple armor and a red helmet, but not both red and green helmets. I need to detect that (1,1) and (1,2) are a conflict, and prevent it from happening again.
I'm half assing examples... here's codeage:
You have a many-to-many relationship and the bad records are fine relationship integrity wise. You cannot create a constraint because the constraint is not based on any one table, and you can't change the schema to make it work. (If you added the slot column to the A_to_G table, you could create a unique index on (adv_id, slot) and that would prevent insertions like you want.)
It's not hard to find the trouble records at least. Not tested but something like this:
That should show you the adventurer ID and slot of any multiples in that slot.