Search code examples
sqlsql-servermany-to-manybridging

Query for retrieving data from a bridging table of many to many relationship

I have a table SQL server with two joining or bridging tables because of the many-to-many relationship.

I wonder if anyone can write the query to retrieve data or perform CRUD operations for this table that updates all these three tables.

Please check the attached Diagram particularly the table (Case, Violence_type, and Referral table in the middle where it has a bridging table). Click to see DB diagram

Yours, omer

Solution

  • So you're trying to link e.g. Case to Referral via the association table Case_Referral? So what's the issue you're facing?

    This is a pretty simple, straightforward SQL statement - SELECT from Case, join on Case_Referral via the case_id key, then join to Referral using the referral_id, and specify which columns from each table you need:

    SELECT
    c.user_name, c.date as CaseDate, c.priority, c.case_status,
    r.date AS ReferralDate, r.referral_name
FROM
    dbo.Case c
INNER JOIN
    dbo.Case_Referral cr ON c.case_ID = cr.case_ID
INNER JOIN
    dbo.Referral r ON cr.referral_ID = r.referral_ID

    So what is the issue / problem you're not understanding?

    You can use the same "technique" to join the other m:n relationships.