Hi,

This may be a stupid question but I can't work out how to get data from two tables when there is a joining table between them I know how to do joins, left joins etc but I'm having a bit of a mental block with this one, and I'm guessing it's an obvious question.

If I have tables called people, organisation and the joining table is called organisationPeople what would the query be to retrieve data from both? I queried the joining table but only got two attributes returned since that is what is stored in the joining table (personId and organisationId)

Any help will be appreciated!

Something like:

select people.*,organisation.* 
from people P, organisation O, organisationPeople OP 
where P.id=OP.peopleid and O.id=OP.organisationid

select people.*,organisation.*
from people P, organisation O, organisationPeople OP
where P.id=OP.peopleid and O.id=OP.organisationid

Thanks again nyef, so basically you need a query that SELECTS everything from people and organisation WHERE the ID in people (P) and in organisation = the Id in the mapping table.

It took me by surprise when I realised I didn't know how to manipulate data in a mapped table because I thought I could do all that just with joining tables, and I haven't needed to use more than a few tables (without mapped tables) up until now.

Is the only advantage to having a mapped table being able to define many to many relationships or are there other advantages? What does roll up mean?

Thanks,

Your actual data is still persisted in your people and organizations tables, so you still need to query them. The third table simply tells you what people go with what organizations.

If I was to INSERT a new person into the people table for a specifc organisation (already listed in the organisation table) would I need to to insert the person data into the people table and the organisationPeople table (mapping table)

If this is the case, how can I INSERT the personId into the mapping table before it has been generated in the people table since the personId is auto_increment? Would I need to first INSERT the person data, and then select * from people table and get the personId and then INSERT the personId in the mapping table?

Thanks,

Sounds like you've got it. You should also be using DRI. You shouldn't be able to add a record to your mapping table that doesn't exist in both of the tables it maps together. In other words, why would you allow a mapping between people who don't exist?

What is a DRI?

When I populate the people table do I need to populate the mapping table between the people table and the organisation table? (each person belongs to an organisation)

Is the mapping table supposed to get updated automatically?

Since the personId is auto_increment, I don't understand how you can insert the personId into the mapping table at the same time it was inserted into the people table.

Declarative referential integrity is cement holding the relationships between your data together. You would establish a primary key on your person table, say on the PersonID column. Then you would establish a foreign key on your mapping table, so that you can't map a person who doesn't exist. This prevents data corruption.

Your mapping table is exactly like any other table in any database. If you want a record to go into it, you have to code that through an insert query. Think about it. Your person table holds information about people. Your organization table holds information about organizations. Your person_in_an_organization table holds information about what people belong to what organizations. Your database server won't know that automatically, so you have to tell it. The fact that person #294 is in organization #19 is just like any other piece of data that you would interact with.

If you're using an identity sequence to generate ID values, you'll need to use an identity function to fetch the value that gets generated in your person table when you add a row to your mapping table. You'll probably want whatever is MySQL's version of Scope_Identity() because you may need to use triggers if your version of MySQL doesn't support DRI.

Thanks for the detailed answer