-1

hope this is not too dumb/simple. I have been practicing joins recently and I have never seen a join done between tables T1, T2 that are in an M:N (Many-Many) relationship. Instead, joins are made between either T1 and the bridge table or between T2 and the bridge table. Is this just a custom, or is it just not possible to do a join between tables with a many-many relationship? I understand the join of T1, T2 along a field F outputs a table T3 containing the different fields/column of T1, T2 while ignoring the common field F. So I don't see why joining T1, T2 along F would be a problem, specially if F was a primary key in each of T1, T2. Is this correct? Thanks in Advance.

EDIT: As Dave points out below, a query dealing with columns from different tables may give rise to ambiguity if this query uses fields F1, F2 from T1, T2 respectively. Still, what if the query is a very simple one where a just want a listing, say of emp_num and emp_jobdescription from T1, T2 resp. ,and not a more complex query involving computations (aggregates, etc.) from the two tables?

Improve this question
5
  • Assuming a related key, you can directly join many to many tables... the question is does this make any sense and are the results deterministic?
    – Dave
    Dec 15, 2015 at 17:44
  • Thanks, ah, good point, I guess the relations between the columns of the resulting tables may be confusing and query results may depend on how we match entries in columns c1, c2 from different tables. Is this correct, Dave? Because, still, the table formed is just a virtual one, used just to do a query, not a permanent one. I am thinking of a simple select query for fields in F1, F2, not one involving aggregates or more complex queries that would use F1, F2 from T1, T2 resp. Is it still true in this restricted case?
    – MSIS
    Dec 15, 2015 at 17:49
  • Sure, there are times when a cross product is desired and you could get that from two separate tables. If you want concrete examples, you should update the question with table definitions, example data. etc. That will always get you better answers on the stack sites.
    – Dave
    Dec 15, 2015 at 18:06
  • Excellent Dave. I will do so soon, will provide data definitions, etc. Thanks. ,
    – MSIS
    Dec 15, 2015 at 18:09
  • Thanks for the anonymous downvote. Congratulations, downvoter, your vote contributes....absolutely nothing to anyone, except for a bit of petty satisfaction to yourself.
    – MSIS
    Dec 15, 2015 at 23:22

2 Answers 2

Reset to default
0

...joins are made between either T1 and the bridge table or between T2 and the bridge table. Is this just a custom, or is it just not possible to do a join between tables with a many-many relationship?

The question to ask isn't, "Are M:N joins possible?" but, "Why is a bridge table being used to join two related tables?"

I'm not sure in what type of environment your SQL installation is being used, but bridge tables are common within data warehousing. They allow the formation of M:N and 1:M types of relationships while still maintaining the appropriate grain of each table.

Doing so also models the data for use in multi-dimensional reporting applications in "cubes" like SSAS or Cognos.

Improve this answer
1
  • Thank you, but I do understand that bridge tables are necessary and I have a good idea for why they are. I am using MSSQL2014.
    – MSIS
    Dec 16, 2015 at 4:15
0

Just how are you going to define a M:N relationship without a bridge table?

If F is a PK of T1 and T2 then you don't even have a many let alone a many to many if you join on F.

Join on a common field G (not a PK or unique) is still not the same
If records T2 rows 5, 6, 7 all have value 'match me' for G you cannot form a relationship to 5 and 7 using that common field G
Common field is commonality and not an explicit relationship

Improve this answer
1
  • Thank you, please give me some time to think it through. I do understand the bridge table is necessary, but let me see about the rest.
    – MSIS
    Dec 16, 2015 at 4:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.