many-to-many query construction

[SnaveBelac]

I have a many-to-many relationship between two tables via a linking table.

The first table is a list of videos. The second table is a list of topics. Each video can be classified under several topics and obviously manhy vides could cover each topic. The linking table just matches videoID's with TopicID's. The videos also have an age attached to them.

I need to run a query (preferably SQL as part of a VBA module) that can pull off videos for a certain age AND/OR a certain topic.

I have tried several SQL queries (built using the access query builder) but have not been successful in retrieving the correct data. I assume I have to use some kind of JOIN but I am a litle confused as to how they work.

Perhaps someone could give me a quick summary as to the differences in INNER abd OUTER joins etc.

Thanks in advance.

----------------------------
SnaveBelac - Adventurer

http://www.hostpipe.co.uk

 

[winston1983]

I would remove the many to many relationship and add a bridge table in the middle. have two fields in it and they should be the topicID and the videoID. this is like a table which makes a one to many relationship from both sides but allows many to many actions. hope this helps.

Regards,


Lewis Harvey
lewis@lharvey.com

 

[MikeRBS]

I would thought you would just join from Topic to Topic-Video to Video. Then just select on topic or whatever. Go into the QBE screen, select the tables in that order, join them and then see what you get. If it doesn't make sense, copy the SQL here.

An inner join just matches existing values. It only produces rows with data present from each participating relation. An outer join creates a row with some missing data when no match is found in the data.

 

[lespaul]

Here's a good article on joins:

'Understanding SQL Joins'

Why don't you post the SQL that you've got?

Leslie

 

[SnaveBelac]

Thank for the responses - much appreciated.

I already had a bridging table set up but was struggling to ge the joins right.

I read the guide provided by lespaul and this helped a lot.

For information the SQL that seems to be working is

SELECT tbl_Program.ProgramID, tbl_Program.P_Title, tbl_Program.P_Age, tbl_Topic.T_Topic, tbl_Topic.TopicID, tbl_Program.P_VideoCode"
FROM tbl_Topic INNER JOIN (tbl_Program INNER JOIN tbl_TopicLink ON tbl_Program.ProgramID = tbl_TopicLink.TL_ProgramID) ON tbl_Topic.TopicID = tbl_TopicLink.TL_TopicID"
WHERE (((tbl_Program.P_Age)=[forms]![frm_main]![cboAge])) OR (((tbl_Topic.TopicID)=[forms]![frm_main]![cboTopic])) OR (((tbl_Program.P_VideoCode)=[forms]![frm_main]![cboCode]));"

This has been created using the query builder so I do not fully understand it but it seems to achieve what I what I want.

I will endeavour to pick it apart and get to grips with it.

Thanks again.



----------------------------
SnaveBelac - Adventurer

http://www.hostpipe.co.uk