1

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

S. Omer Sadaat
  • 15
  • 4

1 Answers1

0

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • 1
    Hi Marc, Your answer and SQL statement resolved my issue. I never used join statements so I was confused about how I could do it with an associate table. Thanks a million, Omer – S. Omer Sadaat Jan 25 '21 at 12:58
  • @S.OmerSadaat: definitely invest some time and effort in getting to know joins - the types and what they can do for you. That's one of the mainstays of any SQL endeavour ! – marc_s Jan 25 '21 at 16:31