As was foretold, we've added advertisements to the forums! If you have questions, or if you encounter any bugs, please visit this thread: https://forums.penny-arcade.com/discussion/240191/forum-advertisement-faq-and-reports-thread/

SQL - cleaning up a relation table

MadpoetMadpoet Registered User regular
edited February 2012 in Help / Advice Forum
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

Posts

  • admanbadmanb unionize your workplace Seattle, WARegistered User regular
    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?

  • InfidelInfidel Heretic Registered User regular
    Is there a reason you can't have C in the first table with A? Do you even need B? Sounds like a potential normalization issue.

    Care to be more specific?

    OrokosPA.png
  • MadpoetMadpoet Registered User regular
    edited February 2012
    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
    

    Madpoet on
  • InfidelInfidel Heretic Registered User regular
    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.

    OrokosPA.png
  • MadpoetMadpoet Registered User regular
    Thanks, that makes sense now that I see it. Haven't tried it, but looks right... case closed.

Sign In or Register to comment.