many-to-many

SQL many-to-many matching

I'm implementing a tagging system for a website. There are multiple tags per object and multiple objects per tag. This is accomplished by maintaining a table with two values per record, one for the ids of the object and the tag. I'm looking to write a query to find the objects that match a given set of tags. Suppose I had the following ...

Many to Many Relationship in MS Dynamics CRM 4.0 - How to?

Hi I'm working on a MS CRM server for a project at my university. What I'm trying to do is to let the user of the CRM to tag some contacts, I thought of creating an entity to archive the tags an to create an N:N relationship between the tag entity and the contact one. I've created and published the new entity and the relationship, but I...

LINQ to entities - Building where clauses to test collections within a many to many relationship.

So, I am using the Linq entity framework. I have to entities "Content" and "Tag" They are in a many-to-many relationship with one another. Content can have many tags and Tags can have many Contents. So I am trying to write a query to select all contents where any tags names are equal to "blah." The entities both have a collection of the...

JPA - many to many relationship of a table with a compound-key with itself

I would like to create a many to many relationship of a table (RoutePlace) with itself using JPA anotations. The singularity of the table I want to join with itself is that it has a compound-key. example: http://h4losw2.files.wordpress.com/2008/10/tables.png Any suggestions? Thanks in advance ...

How to write a SQL statement which gets results via a relationship table? (many to many)

I have 3 tables (archive has many sections, section (may) belong to many archives): archive id PK description archive_to_section archive_id PK FK section_id PK FK section id PK description What would the SQL look like to list all the sections that belong a certain archive id? I am just learning SQL. From what I've read it s...

Many to Many delete cascade in NHibernate

I have a scenario in a system which I've tried to simplify as best as I can. We have a table of (lets call them) artefacts, artefacts can be accessed by any number of security roles and security roles can access any number of artefacts. As such, we have 3 tables in the database - one describing artefacts, one describing roles and a many-...

Interesting Many-many sql join.

I have three related tables "A(id, val)", "B(id, val)", and a link table with a value "AB(aid, bid, val)" I am querying against B to bring back A values, for example: SELECT A.* FROM A INNER JOIN AB ON A.id = AB.aid INNER JOIN B ON AB.bid = B.id WHERE B.val = 'foo'; Every A has many B's and every B has many A's. And the catch that ...

Many-to-many relationships in ASP.NET Dynamic Data

Hey, I need to use a many to many relationship in my project and since Dynamic Data doesn't support it natively, I would like to ask for a sample of an implementation of something like it. I'm specifically looking for a way to both create new instances of the target entity and add a new m2m relationship to a record as well as using exi...

Most succinct LINQ To SQL for taking COUNT(*) of either side of many-to-many?

Please help me with a sanity check. Assuming a many-to-many relationship: What's the most succinct way (using LINQ to SQL) to get a result set showing, for each tag (or post), the aggregate number of posts (or tags) assigned to it? Thanks! ...

Querying ManyToMany relationship with Hibernate Criteria

I'm not sure how to describe this problem, so I think an example is the best way to ask my question: I have two tables with a manyToMany relationship: DriversLicence <-> LicenceClass LicenceClass is things like "Car", "Motorbike", and "Medium Rigid". Using Hibernate Criteria, how can I find all licences that have both "Car" and "Moto...

How to deal with a Many-To-Many Relation in my API

I have two entities Foo and Bar with a Many to Many relationship between them. Let's say there is no semantic argument for why Foo might be "responsible" for the many to many relationship, but we arbitrarily decide that Foo is responsible for the relation (I.e., in NHibernate we would mark Bar as Inverse) That's all well and good from ...

MySQL strict select of rows involving many to many tables

I have three tables in the many-to-many format. I.e, table A, B, and AB set up as you'd expect. Given some set of A ids, I need to select only the rows in AB that match all of the ids. Something like the following won't work: "SELECT * FROM AB WHERE A_id = 1 AND A_id = 2 AND A_id = 3 AND ... " As no single row will have more than on...

Dynamic Data Filter on many-to-many relationship, how?

I am using Dynamic Data with Linq to SQL, and have two tables: Team and TeamMember. From the user's perspective, its a simple one-to-many relationship, but on the database, a many-to many table sits between them, recording on which dates a member belonged to a team, using From- and To dates. How do I add a Team dropdown filter on the L...

SQL how to search a many to many relationship

I have a database with two main tables notes and labels. They have a many-to-many relationship (similar to how stackoverflow.com has questions with labels). What I am wondering is how can search for a note using multiple labels using SQL? For example if have a note "test" with three labels "one", "two", and "three" and I have a seco...

Twitter-like "follow user" and "watch this" problem

What's the best way to handle many-to-many trigger relationships like the Twitter "follow this user" problem. I have a similar problem with users "watching" threads for replies. If there are 10,000 users watching a thread, and someone replies, how best to notify the watchers? All I can think of is the following: Upon insert, check th...

Help with many-to-many relation

I have a problem with a many-to-many relation in my tables, which is between an employee and instructor who work in a training centre. I cannot find the link between them, and I don't know how to get it. The employee fields are: employee no. employee name company name department job title business area mobile number ext ranking The ...

Linq2Sql Many:Many question, How would you do this?

I know many:many isn't supported in Linq2Sql but I am working on a workaround I am working with my little SO clone and I have a table with Questions and a table with Tags and a linking table QuestionTag so I have a classic many:many relationship between Questions and Tags. To display the list of Questions on the front page I have this...

How do you do many to many table outer joins?

I have 3 tables, foo, foo2bar, and bar. foo2bar is a many to many map between foo and bar. Here are the contents. select * from foo +------+ | fid | +------+ | 1 | | 2 | | 3 | | 4 | +------+ select * from foo2bar +------+------+ | fid | bid | +------+------+ | 1 | 1 | | 1 | 2 | | 2 | 1 | | 2 | 3...

How do I implement a many-to-many association from an entity to itself in Entity Framework?

I have an entity that exposes a collection of children of the same type. The entity can itself be a child of another instance - in effect a many-to-many relationship with itself. In the database I have a linker table with two columns - parentId and childId - both of which reference the Id column in my entity table. Entity Framework corre...

Code review for many-to-many relationship UI implementation in CoreData

Below is the "work horse" piece of code that I've implemented in order to display a many-to-many relationship in CoreData. My UI is on a screen called "Expense". This expense can be associated with many transactions. (Also, transactions can be associated with several expenses). The UI contains the currently selected expense, and a table...