1

I am trying to perform a Create, Select, Update and Delete operation on a many to many relationship with an intermediary table (associative table). I have the following structure. Assume FK constraints in the intermediary table where the names match.

tbl_A and tbl_B have a many to many relationship. tbl_IM is the intermediary table.

tbl_A
aID 
Name

tbl_B
bID
Name
UniqueField

Intermediary Table tbl_IM
imID
aID
UniqueField

a tbl_A item can have many tbl_B items, and a tbl_B item can belong to many tbl_A items.

tbl_B is generally a fixed set of 18 records or so (for allowable selections in a program).

How do I Select and Update all tbl_B items that belong to a particular tbl_A item? How do I get the whole set of tbl_A items along with their associated tbl_B items?

Please do not use wild cards (Select *). My example has only 2 fields each for brevity; I need to see how it is done by using fields. Casting is acceptable--so P1.aID is fine.

I have looked at question Select in a many-to-many relationship in MySQL but it is not clear to me how it applies to my situation.

Community
  • 1
  • 1
Ken
  • 2,518
  • 2
  • 27
  • 35
  • We need to know what the tables mean. When a row is in t tbl_A, what does it state? "AID identifies an a-item named NAME"? When a row is in tbl_B, what does it state? "BID identifies a b-item named NAME with additional identifier UNIQUEFIELD"?? When a row is in tbl_IM what does it state? "IMID uniquely identifies the a-item-b-item pair where a-item AID owns the b-item with additional identifier UNIQUEFIELD"?? – philipxy Jul 07 '16 at 19:59
  • @philipxy aID is PKey, bID is PKey, imID is PKey.. UniqueField is a field in the the table that is Unique. The field names in the Intermediary Table that match the names of the other two tables are FKeys in the Intermediary table to their respective table. – Ken Jul 07 '16 at 20:17
  • You didn't answer my questions. PS It is not necessary to know CKs, PKs, UNIQUEs, FKs or other constraints to query/update. PPS It's not clear whether UniqueField is a unique field in tbl_IM or it's just called that because of a same-named column in tbl_B that is unique there. Better to give it some other name and declare unique column sets separately per table. Not that this affects what query you need. – philipxy Jul 07 '16 at 20:29

1 Answers1

1

Create A view that will display the fields you want

CREATE VIEW combined AS SELECT tbl_A.name, tbl_B.bName
FROM tbl_B INNER JOIN (tbl_A INNER JOIN tbl_M ON tbl_A.ID = tbl_M.AId) ON tbl_B.unique = tbl_M.Unique;

Then you can perform CRUD operation on the view as if it was a table

BAKARI SHEGHEMBE
  • 449
  • 6
  • 20
  • This looks like a nice option I am not sure how it will work - I never used them before; does this view get updated when my tables are updated or only when the update is run on the view ? – Ken Jul 07 '16 at 20:20
  • 1
    A view in database is what we call the logical schema, while the table is the physical schema. A view behaves like a table, the main difference being it exists virtually. To answer your question, yes changes made on the table will also appear on the view – BAKARI SHEGHEMBE Jul 07 '16 at 20:27
  • ok I think I understand it ; makes it easier to update / manage the logical relationship rather than all the individual related tables. This is not the same as a temp table i.e. a cursor would that be correct ? I will mark this as answer - because for one the select by itself works magically - I am thinking in C# I can bind a DGV to the View and do my crud more easily with out using EFramework. – Ken Jul 07 '16 at 20:48