A couple of things.
Your foreign keys will be silently ignored if you use MyISAM engine. You have to use InnoDB for them to work.
You should us the same names for the fields linked with a foreign key. It'll drive you mad trying to remember the names if they are different in the two tables.
To enforce the event_id / Mem_id being unique in memevents, just set it as the primary key.
code tags for the forum start and end with square brackets [ ] not < >
All that said, here's one way to find the kids not listed for event_id 1 'Regular Meeting'
SET FOREIGN_KEY_CHECKS=0;
DROP TABLE If Exists Events;
CREATE TABLE Events (
Event_ID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
Event_Title VARCHAR (100),
PRIMARY KEY (Event_ID)
)
ENGINE = Innodb;
DROP TABLE If Exists Members;
CREATE TABLE Members (
Mem_ID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
FName VARCHAR(100) NOT NULL,
LName VARCHAR(100) NOT NULL,
Age INTEGER UNSIGNED,
Grade INTEGER UNSIGNED,
PRIMARY KEY (Mem_ID)
)
ENGINE = Innodb;
DROP TABLE If Exists MemEvent;
CREATE TABLE MemEvent (
Event_ID INTEGER UNSIGNED NOT NULL,
Mem_ID INTEGER UNSIGNED NOT NULL,
PRIMARY KEY (Event_ID,Mem_ID),
FOREIGN KEY FK_EventID (Event_ID) REFERENCES Events(Event_ID),
FOREIGN KEY FK_MemID (Mem_ID) REFERENCES Members(Mem_ID)
)
ENGINE = Innodb;
INSERT INTO Events VALUES(1,'Regular Meeting')
,(2,'Zoo Field Trip')
,(3, 'Science Center Field Trip')
,(4,'Swim Meet')
,(5,'Library Field Trip');
INSERT INTO Members VALUES(1,'Sam','Smith',13,6)
,(2,'Edith','Jones',NULL,NULL)
,(3,'Joe','Moore',NULL,3)
,(4,'Tom','Lee',15,NULL)
,(5,'Adam','Jones',12,5);
INSERT INTO MemEvent VALUES(0001 , 0001)
,(0001 , 0002)
,(0002 , 0003)
,(0002 , 0002)
,(0003 , 0004)
,(0003 , 0005)
,(0004 , 0001)
,(0004 , 0005)
,(0005 , 0001)
,(0005 , 0002)
,(0005 , 0003)
,(0005 , 0004)
,(0005 , 0005);
SET FOREIGN_KEY_CHECKS=1;
SELECT m.fname
, m.lname
FROM members m
LEFT JOIN (SELECT mem_id, event_id FROM memevent WHERE event_id = 1) me
ON me.mem_id = m.mem_id
WHERE me.mem_id IS NULL
;
+-------+-------+
| fname | lname |
+-------+-------+
| Joe | Moore |
| Tom | Lee |
| Adam | Jones |
+-------+-------+
3 rows in set (0.02 sec)
Good luck,
Barry.
Edited 1 time(s). Last edit at 06/07/2012 03:42AM by Barry Galbraith.