MySQL Forums
Forum List  »  PHP

Re: A Kind of Reverse Join
Posted by: Barry Galbraith
Date: June 06, 2012 08:08PM

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.

Options: ReplyQuote


Subject
Written By
Posted
June 05, 2012 01:35PM
June 06, 2012 01:33PM
June 06, 2012 01:31PM
Re: A Kind of Reverse Join
June 06, 2012 08:08PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.