Creating SQL query with one-to-many field

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
slaterino
Forum Commoner
Posts: 46
Joined: Fri Jul 11, 2008 10:50 am

Creating SQL query with one-to-many field

Post by slaterino »

Okay, I'm hoping someone might be able to help with this. I've tried trawling the web but it's quite a hard problem to look for and I haven't managed to find a solution yet. I have an append query that adds four fields to a table. However there is one issue because schSessionID and schSchID have a one-to-many relationship. It means that the query works fine when there is only one occasion of schSchID for a schSessionID but when there are multiple nothing gets added.

It is vital that a new entry is created for every single schSchID. How can I change this query so that this happens? It doesn't seem like it should be that complicated but I just can't figure it out!

Code: Select all

INSERT INTO tbl_course_attendees ( URN, [current], course_ID, session_ID )
SELECT Forms!frm_register!Combo2, -1, schSessionID, schSchID
FROM tbl_activity_schedule
WHERE schSessionID = Forms!frm_register!Combo0;
Thanks!
Russ
Last edited by califdon on Wed Sep 08, 2010 3:16 pm, edited 2 times in total.
Reason: Changed [code] to [syntax=sql] tag for readability
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Creating SQL query with one-to-many field

Post by califdon »

You didn't say, but I recognize that this is an Access database (because of the reference to "Form!"). First, an append query can't add fields to a table, it adds new records. You have not identified your actual issue accurately. What does schSchID represent? To understand your problem, we need to know what the key fields (primary and foreign) are in each of the tables. The definition of a one-to-many relationship is that one of the key values must be unique in the table where it is the primary key. Otherwise, relational mathematics and queries fail. Tell us what the primary key is in each table and what foreign keys exist.
Post Reply