locked
Many To Many relationship RRS feed

  • Question

  • Hi all

    If we have two table A and B, these two table related to each other , now I have another table ( bridge table )  that link all record in table A to each record in table B (But I need to do that automatically) that mean every new record in table A or B will be automatically linked to all record in the other table, I try the INNER JOIN statement and the result of query give may the what I need from bridge table But how to let the bridge table do that automatically  

    thanks

    Wednesday, September 28, 2011 9:14 AM

Answers

  • You need to create the triggers on each table such that it can find a record in other table and use newly insterted record . This will then be added to third table automatically.

     

    I have gievn code for the Department table trigger, similary you can write one trigger for stage table.

    CREATE

    TRIGGER dbo.OnInsertNewRecord

     

    ON dbo.Department

     

    AFTER INSERT

    AS

    BEGIN

     

    -- SET NOCOUNT ON added to prevent extra result sets from
     

     

    -- interfering with SELECT statements.
     

     

    SET NOCOUNT ON;
     

    Declare

    @DepID as

    int

    Declare

    @Office as

    Int

    Declare

    @DSName as varchar(50

    )

    select

    top 1

    @DepID

    = DepID,

     

    @DSName

    =DepName,
     

    @Office

    = OfficeID

    from

    Inserted

     

    -- Insert statements for trigger here

    insert

    into Dep_Stage (DepID, StageID, OfficeID, DSName)

    select

    @DepID, Stage.StageID, @Office, @DSName + '_' + StageName From Stage

     

    where OfficeID =@Office

    END

    GO

     

    Hopefully this will resolve your problem.

    Regards,

    Sushil Saini

    Grok Innovations

    www.grokinnovations.co.uk


    Sushil Saini Grok Innovations Uk Ltd www.grokinnovations.co.uk
    • Marked as answer by Hassan Ghazy Wednesday, September 28, 2011 2:16 PM
    Wednesday, September 28, 2011 12:00 PM

All replies

  • To get the data only from the bridge table rather than tables A and B, you have to put all the columns of tables A and B into bridge table. At that time, simple select statement will do.

    Regards.

    Wednesday, September 28, 2011 9:24 AM
  • thanks airface12

    I did that But where I'd but the select statement  to get the data from table A and B ? Should I but the statement in trigger or where? and how I sure that is no duplicate record in bridge table will be made ? and if I have a column that don't allow null value in bridge table how I let the column get auto integer to use it as PK

    Thanks and Regards


    • Edited by Hassan Ghazy Wednesday, September 28, 2011 9:56 AM
    Wednesday, September 28, 2011 9:45 AM
  • Please provide us your table definitions and sample data.


    Thanks & Regards Prasad DVR
    Wednesday, September 28, 2011 9:57 AM
  • There are the table and simple data

    Table A : Department

    DepID(pk)          DepName        OfficeID

       1                        Civil                  1

       2                        Survey              2

       3                        Electronic          1

     

     Table B: Stage

    StageID(pk)          stageName        OfficeID

       1                        First                     1

       2                        Second                1

       3                        Third                    2

    this query what i need

    SELECT    Department.DepID, Stage.StageID, Stage.OfficeID
    
    From        Department  INNER JOIN Stage
    
    on  Department.OfficeID = Stage.OfficeID
    

    Now How I insert the result of statment above in table C (Dep_Stage) automatically, that mean where user will add new record to table A or table B, the record will automatically insert in table C  based on statement above

    Table C: Dep_Stage

    DSID(pk)          DepID        StageID         OfficeID       DSName                  

     

    Wednesday, September 28, 2011 10:34 AM
  • HI Hassan !
     
    You may get the desired ouput using below;

    CREATE TABLE #Department(DepID INT,DepName VARCHAR(20),OfficeID INT)
    CREATE TABLE #Stage(StageID INT,StageName VARCHAR(20),OfficeID INT)
    CREATE TABLE #Dep_Stage(DSID INT,DepID INT,StageID INT,OfficeID INT,DSName VARCHAR(20))
    
    INSERT INTO #Department
    SELECT 1,'Civil',1 UNION ALL
    SELECT 2,'Survey',2 UNION ALL
    SELECT 3,'Electronic',1 
    
    INSERT INTO #Stage
    SELECT 1,'First',1 UNION ALL
    SELECT 2,'Second',1 UNION ALL
    SELECT 3,'Third',2
    
    --Put this in some usp and then schedule a job to run it for every min or for every 2 mins.
    INSERT INTO #Dep_Stage
    SELECT D.DepID,S.StageID,S.OfficeID
    FROM #Department D
    INNER JOIN #Stage S ON S.OfficeID = D.OfficeID
    EXCEPT
    SELECT DepID,StageID,OfficeID FROM #Dep_Stage
    
    


    Please let me know if this doesn’t work for you. Hope I have answered you correctly.

    Thanks, Hasham

    Wednesday, September 28, 2011 11:06 AM
    Answerer
  • Your base tables have one column in common but in neither table is that column unique, so I don't see the use of a bridge table.

    The best you can do is run a cross join to return the cartesian product of the matching rows.


    Experience is a bad teacher whose exams precede its lessons.
    Wednesday, September 28, 2011 11:07 AM
  • Hi Hasham and thank you

    I try The insert statment but In table Dep_Stage I have The DSID column that don't allow null value, so how I let the table Get auto value for this column? And when I'd but the insert statement?  

    Wednesday, September 28, 2011 11:21 AM
  • HI Hassan !

    Please let me know , what is your Key combination for (DSID[PK]) , do you want to concatenate DeparmentID with StageID in ths field.

     

    Thanks, Hasham

    Wednesday, September 28, 2011 11:38 AM
    Answerer
  • Any Key Combination that ensure give him a unique value
    Wednesday, September 28, 2011 11:46 AM
  • HI Hassan !
     
    you may simply define it as IDENTITY. You may use below;

    CREATE TABLE #Dep_Stage(DSID INT IDENTITY(1,1),DepID INT,StageID INT,OfficeID INT,DSName VARCHAR(20))
    

    Please let me know if this doesn’t work for you. Hope I have answered you correctly.

    Thanks, Hasham

    Wednesday, September 28, 2011 11:52 AM
    Answerer
  • You need to create the triggers on each table such that it can find a record in other table and use newly insterted record . This will then be added to third table automatically.

     

    I have gievn code for the Department table trigger, similary you can write one trigger for stage table.

    CREATE

    TRIGGER dbo.OnInsertNewRecord

     

    ON dbo.Department

     

    AFTER INSERT

    AS

    BEGIN

     

    -- SET NOCOUNT ON added to prevent extra result sets from
     

     

    -- interfering with SELECT statements.
     

     

    SET NOCOUNT ON;
     

    Declare

    @DepID as

    int

    Declare

    @Office as

    Int

    Declare

    @DSName as varchar(50

    )

    select

    top 1

    @DepID

    = DepID,

     

    @DSName

    =DepName,
     

    @Office

    = OfficeID

    from

    Inserted

     

    -- Insert statements for trigger here

    insert

    into Dep_Stage (DepID, StageID, OfficeID, DSName)

    select

    @DepID, Stage.StageID, @Office, @DSName + '_' + StageName From Stage

     

    where OfficeID =@Office

    END

    GO

     

    Hopefully this will resolve your problem.

    Regards,

    Sushil Saini

    Grok Innovations

    www.grokinnovations.co.uk


    Sushil Saini Grok Innovations Uk Ltd www.grokinnovations.co.uk
    • Marked as answer by Hassan Ghazy Wednesday, September 28, 2011 2:16 PM
    Wednesday, September 28, 2011 12:00 PM
  • Thanks again Hasham

    The table Dep_stage already exist and the DSID is INT and had an Identity(1,1) but when i try the insert statement above i get error that the DSID column dos not except null value

    Thanks Sushil Saini, I'll try it


    • Edited by Hassan Ghazy Wednesday, September 28, 2011 2:38 PM
    Wednesday, September 28, 2011 12:06 PM
  • HI Hassan !
     
    Just change the INSERT statement to be based on Column Specifc like below;

    INSERT INTO #Dep_Stage(DepID,StageID,OfficeID)
    SELECT D.DepID,S.StageID,S.OfficeID
    FROM #Department D
    INNER JOIN #Stage S ON S.OfficeID = D.OfficeID
    EXCEPT
    SELECT DepID,StageID,OfficeID FROM #Dep_Stage
    

    Please let me know if this doesn’t work for you. Hope I have answered you correctly.
     
    Thanks, Hasham

    Wednesday, September 28, 2011 1:07 PM
    Answerer
  • Hi Sushil Saini It work like magic, thank you

    I have one more Question If you have time, If i want to delete some record in table Department Or table Stage, I cant untel I delete the record related to in table Dep_stage, What the trigger will be ?

    Hasham Thank you for you time too


    Wednesday, September 28, 2011 2:36 PM
  • >> If we have two table A and B, these two table related to each other, now I have another table (bridge table [sic]) that link all record [sic] in table A to each record [sic] in table B (But I need to do that automatically) that mean every new record [sic] in table A or B will be automatically linked [sic] to all record [sic] in the other table, I try the INNER JOIN statement and the result of query give may the what I need from bridge table [sic] But how to let the bridge table [sic] do that automatically. <<
    Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. If you know how, follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect. 
    Sample data is also a good idea, along with clear specifications. It is very hard to debug code when you do not let us see it. If you want to learn how to ask a question on a Newsgroup, look at: http://www.catb.org/~esr/faqs/smart-questions.html Her are a few of the things you got wrong: 
    1. Rows are not records.
    2. There is no such thing as a “bridge table” in RDBMS 
    3. Links are a concept from the old network databases and not RDBMS. 
    Since you don't know anything about RDBMS, then get a copy of the simplest intro book I know -- 
    http://www.amazon.com/Manga-Guide-Databases-Mana-Takahashi/dp/1593271905
    It is hard to talk to you when you do not know the right words or concepts. Tables model entities or relationships, not both. I think what you are calling a “bridge table” is a relationship. But what kind of relationship? Your vague, useless narrative implies it is binary and m:n but is it  Mandatory or Optional? 
    For example, here are two skeleton tables:
    CREATE TABLE Boys
    (boy_name VARCHAR(30) NOT NULL PRIMARY KEY
    ..);
    CREATE TABLE Girls
    (girl_name VARCHAR(30) NOT NULL PRIMARY KEY,
    .. );
    Yes, I know using names for a key is a bad practice, but doing so will make my examples easier to read. You can make a lot of different many-to-many relationships between these two tables. (If you don't believe me, just watch the Jerry Springer show some time.) The simplest relationship table looks like this:
    CREATE TABLE Pairs
    (boy_name INTEGER NOT NULL
    REFERENCES Boys (boy_name)
     ON DELETE CASCADE
     ON UPDATE CASCADE,
    girl_name INTEGER NOT NULL,
    REFERENCES Girls(girl_name)
     ON DELETE CASCADE
     ON UPDATE CASCADE);
    The "Pairs" table let’s you insert rows like this:
    ('Joe Celko', 'Brooke Shields')
    ('Joe Celko', 'Kim Bassinger')
    ('Alec Baldwin', 'Kim Bassinger')
    ('Joe Celko', 'Brooke Shields')
    Oops! I am shown twice with 'Brooke Shields' because the "Pairs" table does not have its own key. This mistake is easy to make, but how to fix it is not always obvious.
    The "Orgy" table gets rid of the duplicated rows and makes this a proper table:
    CREATE TABLE Orgy
    (boy_name INTEGER NOT NULL
    REFERENCES Boys (boy_name)
     ON DELETE CASCADE
     ON UPDATE CASCADE,
    girl_name INTEGER NOT NULL,
    REFERENCES Girls(girl_name)
     ON DELETE CASCADE
     ON UPDATE CASCADE,,
    PRIMARY KEY (boy_name, girl_name)); -- compound key
    The primary key for the table comprises two or more columns and is called a compound key because of that fact. We can immediately see that your request is absurd. How would the DDL decide to match me with 'Brooke Shields', 'Kim Bassinger' or both without any other information? 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL
    Wednesday, September 28, 2011 2:45 PM
  • Thanks, That is also possible using on delete triggers , I will script it tomorrow and send over. In the mean time can you vote on my answer.

     

    Regards,

     

    Sushil

     

    www.grokinnovations.co.uk


    Sushil Saini Grok Innovations Uk Ltd www.grokinnovations.co.uk
    Wednesday, September 28, 2011 3:34 PM
  • It is hard to talk to you when you do not know the right words or concepts. Tables model entities or relationships, not both. I think what you are calling a “bridge table” is a relationship. But what kind of relationship? Your vague, useless narrative implies it is binary and m:n but is it  Mandatory or Optional? 
    Thanks Celko for your time, the other people Know what I talk about and they give me the solution to what i need exactly, so pleas before submit a comment and waste your time , read carefully all other comment !!
    Wednesday, September 28, 2011 3:38 PM
  • Thanks, That is also possible using on delete triggers , I will script it tomorrow and send over. In the mean time can you vote on my answer.


    Thanks Sushil, I really appreciate your help
    Wednesday, September 28, 2011 3:41 PM
  • HI Hassan !

    What Celko was saying is you and we all need to learn to avoid making mistakes we make in our work. Please consider this as a sincere advise from him nothing personal.

    Thanks, Hasham

    • Proposed as answer by Naomi N Wednesday, September 28, 2011 3:53 PM
    • Unproposed as answer by Hassan Ghazy Wednesday, September 28, 2011 8:05 PM
    Wednesday, September 28, 2011 3:52 PM
    Answerer
  • It is hard to talk to you when you do not know the right words or concepts. Tables model entities or relationships, not both. I think what you are calling a “bridge table” is a relationship. But what kind of relationship? Your vague, useless narrative implies it is binary and m:n but is it  Mandatory or Optional? 
    Thanks Celko for your time, the other people Know what I talk about and they give me the solution to what i need exactly, so pleas before submit a comment and waste your time , read carefully all other comment !!
    When someone who wrote the ANSI/ISO Standards, over 1200 articles on RDBMS, eight books on SQL and 35+ years of work experience, tells you that you are doing it all wrong, you might want to listen. :)
    Oh, nobody actually helped you and you still have not posted any DDL. Table names are collective or plural because they model sets as a whole and not records, so you might have posted this incorrect DDL:
    CREATE TABLE Departments
    (dept_id INTEGER NOT NULL PRIMARY KEY,
     dept_name VARCHAR(20) NOT NULL, 
     office_id INTEGER NOT NULL);
    CREATE TABLE Stages
    (stage_id INTEGER NOT NULL PRIMARY KEY,
     stage_name VARCHAR(20) NOT NULL, 
     office_id INTEGER NOT NULL);
    One of the many, many ways that SQL is not like record processing tape files is that tables do not have to be physical. Instead of making a deck of punch cards in SQL, like you want to do, SQL programers use a VIEW. You even wanted to put a silly fake record number “ds_id” on each punch card! What is the proper name of this this thing? It is not “Department_Stage”; did you know that hyphenated convention comes from the old network databases? It is how they named “linkage sets” in NDL. Not relational at all. I will guess that “Projects” is a descriptive, collective name for the virtual table: 
    CREATE VIEW Projects (dept_id, stage_id, office_id)
    AS 
    SELECT D.dept_id, S.stage_id, S.office_id
      FROM Departments AS D, Stages AS S
     WHERE D.office_id = S.office_id; 
    And this is STILL a stinking kludge! Think about an Office; it is an entity Entities have tables, but you made it into an attribute. This is a classic “three legged stool” schema, which appears in the section on 4NF and 5NF in every textbook on RDBMS. The schema should probably look like this: 
    CREATE TABLE Departments
    (dept_id INTEGER NOT NULL PRIMARY KEY,
     dept_name VARCHAR(20) NOT NULL, 
     ..);
    CREATE TABLE Stages
    (stage_id INTEGER NOT NULL PRIMARY KEY,
     stage_name VARCHAR(20) NOT NULL, 
     ..);
    CREATE TABLE Offices
    (office_id INTEGER NOT NULL PRIMARY KEY,
     office_name VARCHAR(20) NOT NULL, 
     ..);
    CREATE TABLE Projects
    (project_name VARCHAR(15) NOT NULL UNIQUE,
     dept_id INTEGER NOT NULL 
       REFERENCES Departments (dept_id)
       ON DELETE CASCADE
       ON UPDATE CASCADE,
     stage_id INTEGER NOT NULL 
       REFERENCES Departments (stage_id)
       ON DELETE CASCADE
       ON UPDATE CASCADE,
     office_id INTEGER NOT NULL 
       REFERENCES Offices (office_id)
       ON DELETE CASCADE
       ON UPDATE CASCADE,
     PRIMARY KEY (dept_id, stage_id, office_id)
    );
    Notice how DRI actions and not procedural triggers make sure that you do not have anomaly problems? Real keys and not fake records  numbers? 
    Again, you are dangerous ignorant and apparently proud it. Why don't you go to your boss, show him this thread and tell him that a world famous expert said you need to get an education or to be fired. Let him make a choice. Yes, you are that bad an SQL programmer. 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL
    Wednesday, September 28, 2011 6:59 PM
  • Oh my God, you are really get all think wrong, The name of table there are example nothing  more, and the office in example are a table that other table  A and B are related to by OfficeID, not just a silly entity. and when you or any body take a look to the simple data above it so obvious the OfficeID record are related to another table but I don't know if you get that or just jump to a conclusion that are it's just silly number. And I'm asking about a specific issue where you talk another thing, and when I told you I get what i need for my issue, you back and insists by talk about how to create a table or named or fake record number

    Sir I know how to create table, and I know relationship,Yes I'm not an expert But not that BAD, and I'm not working in data base or programming or study it in school , I'm a civil engineer, it just a hobby and just learn, so pleas I have an issue and get the solution and that is END

    Wednesday, September 28, 2011 9:08 PM
  • Again, if you can read past the stuff that makes you angry, you will see there's nothing wrong with what Joe is conveying to you. You may not like his style, but he is right.

    In forums, we can only judge stuff based on what you are communicating, we cannot ascertain whether you are an experienced SQL Server developer who do this for a living or a civil engineer with an interest in SQL. That stuff is irrelevant on the forums.

    Just like in civil engineering, we have nomenclations and standards, "things" have actual names, for example, we don't use the term "bridge table", but rather relations, attributes, and domains that describe how information is modeled; tables have definitions, we express those definitions using DMLs and DDLs. You won't want us developers calling corrosion, rust for example, not knowing what sort of material we are referring to. As a civil engineer you are able to discern the proper terms, sort of what we kind of expect here.

    You also have to keep in mind that we cannot see what you are seeing and have no access to your data, therefore it's important for you to post sample data, even if you think it's trivial. What's trivial to you may not be as apparent to us.


    MG.-
    Mariano Gomez, MIS, MCITP, PMP
    IntellPartners, LLC.
    http://www.intellpartners.com/
    Blog http://dynamicsgpblogster.blogspot.com/
    Wednesday, September 28, 2011 9:43 PM
  • Dear Mariano,

    I'm not angry, but I wonder If I already get what I asking for, so what the point of his comment ? I'm here in forums to learn not to judgmental, and when people like Sushil see what I talk about and give me the exact what I need, while Joe talk about something else. I don't know what point he want to make ? If he cant understand my issue there are no need to writes about some thing else, there are who understand it and solve it

    Regards,

     


    • Edited by Hassan Ghazy Wednesday, September 28, 2011 10:28 PM
    Wednesday, September 28, 2011 10:27 PM
  • As you said, you are here to learn and no one should have the right to put you down. However, part of our responsibilities as MVPs, forum moderators, and the likes of anyone who helps you with an answer, is to make sure you are learning best practices in the process. Some individuals might gladly provide you with an answer so long you are happy with it, others, will go the extra mile to make sure you learn how to do things the right way, from the get go - I am not singling out anyone here by the way.

    We all have different styles and different approaches and THAT'S the beauty of the forums... there's no other place you will learn to do things in different ways, but here! Not in books, not in tutorials, not in the classroom. Hence, you must have a bit of an open mind and simply avoid taking things personally. Even I have learned that lesson and continue to learn from everyone here. 


    MG.-
    Mariano Gomez, MIS, MCITP, PMP
    IntellPartners, LLC.
    http://www.intellpartners.com/
    Blog http://dynamicsgpblogster.blogspot.com/
    Wednesday, September 28, 2011 11:41 PM
  • To manage delete record in  department, following trigger can automatically delete records in DEP_stage.

     

    ALTER

    TRIGGER [dbo].[TriggerOnDepartmentDeletion]

     

    ON [dbo].[Department]

     

    AFTER Delete

    AS

    BEGIN

     

    -- SET NOCOUNT ON added to prevent extra result sets from
     

     

    -- interfering with SELECT statements.
     

     

    SET NOCOUNT ON;
     

    Declare

    @OfficeID as

    int

    Declare

    @DepID as

    int

    Declare

    @DepName as varchar(100

    )

    Select

    @OfficeID

    = OfficeID ,

    @depID

    = DepID,
     

    @DepName

    = DepName
     

    From

    Deleted

    Delete

    from Dep_Stage where DepID= @depID and OfficeID=@OfficeID

     

    -- Insert statements for trigger here

    END

    Regards,

    Sushil

    Grok Innovations

    www.grokinnovations.co.uk


    Sushil Saini Grok Innovations Uk Ltd www.grokinnovations.co.uk
    Thursday, September 29, 2011 8:09 AM