SQL confused on foreign key/references/constraints, newb question.

[voidflux]

Thanks for the responce! Now i'm lost on how I would get data from 4 of the tables.


I'm trying to get all the data that link these tables together. They all have forgien keys in common so I would like to retireve all that data and print it out to the screen.

Here's the code used to create the tables as well as the statement I used to query the databases.

CREATE TABLE `entity` (
`entityId` int(11) NOT NULL,
`mainNodeEntityId` int(11) default NULL,
`entityName` varchar(255) collate utf8_bin NOT NULL,
`domainMgrId` int(11) NOT NULL,
`entityType` int(11) NOT NULL,
`createTime` datetime NOT NULL,
`changeTime` datetime NOT NULL,
`displayLabel` varchar(255) collate utf8_bin NOT NULL,
`description` varchar(512) collate utf8_bin default NULL,
`alias` varchar(255) collate utf8_bin default NULL,
PRIMARY KEY (`entityId`),
UNIQUE KEY `entity_uk` (`domainMgrId`,`entityName`),
KEY `entityMainNode_idx` (`mainNodeEntityId`,`entityId`),
KEY `entityEntityType_idx` (`entityType`),
KEY `entityName_idx` (`entityName`),
KEY `entityDomain_idx` (`domainMgrId`),
CONSTRAINT `entity_domainMgr_fk` FOREIGN KEY (`domainMgrId`) REFERENCES `domainMgr` (`domainMgrId`) ON DELETE CASCADE,
CONSTRAINT `entity_entityType_fk` FOREIGN KEY (`entityType`) REFERENCES `entityType` (`entityType`) ON DELETE CASCADE,
CONSTRAINT `entity_entity_fk` FOREIGN KEY (`mainNodeEntityId`) REFERENCES `entity` (`entityId`) ON DELETE CASCADE,
CONSTRAINT `entity_entiytNameCache_fk` FOREIGN KEY (`entityId`) REFERENCES `entityNameCache` (`entityId`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin |

CREATE TABLE `connects` (
`connectionId` int(11) NOT NULL auto_increment,
`aEndEntityId` int(11) NOT NULL,
`zEndEntityId` int(11) NOT NULL,
`unidirectional` int(11) NOT NULL,
PRIMARY KEY (`connectionId`),
UNIQUE KEY `connects_uk` (`aEndEntityId`,`zEndEntityId`,`unidirectional`),
KEY `connEntityA_idx` (`aEndEntityId`,`zEndEntityId`,`connectionId`),
KEY `connEntityZ_idx` (`zEndEntityId`,`aEndEntityId`,`connectionId`),
CONSTRAINT `connects_entity_fk1` FOREIGN KEY (`aEndEntityId`) REFERENCES `entity` (`entityId`) ON DELETE CASCADE,
CONSTRAINT `connects_entity_fk2` FOREIGN KEY (`zEndEntityId`) REFERENCES `entity` (`entityId`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin

CREATE TABLE `topologyLinks` (
`entityId` int(11) NOT NULL,
`connectionId` int(11) NOT NULL,
PRIMARY KEY (`entityId`,`connectionId`),
KEY `topoLinkConnId_idx` (`connectionId`,`entityId`),
CONSTRAINT `topologyLinks_connects_fk` FOREIGN KEY (`connectionId`) REFERENCES `connects` (`connectionId`) ON DELETE CASCADE,
CONSTRAINT `topologyLinks_entity_fk` FOREIGN KEY (`entityId`) REFERENCES `entity` (`entityId`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin

CREATE TABLE `networkPipe` (
`entityId` int(11) NOT NULL,
`connectionId` int(11) NOT NULL,
`aggregationType` int(11) NOT NULL,
PRIMARY KEY (`entityId`,`connectionId`),
KEY `netPipeConnId_idx` (`connectionId`,`entityId`),
CONSTRAINT `networkPipe_connects_fk` FOREIGN KEY (`connectionId`) REFERENCES `connects` (`connectionId`) ON DELETE CASCADE,
CONSTRAINT `networkPipe_entity_fk` FOREIGN KEY (`entityId`) REFERENCES `entity` (`entityId`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin

I'm trying to retrieve all the data that map to entity; so I need to link
all the forgien keys and all that table data and spit it out to the screen.

Here is what I tired:
select * from entity, connects, networkPipe, topologyLinks where
entity.entityId = connects.aEndEntityId or entity.entityId = connects.zEndEntityId
and entity.entityId = networkPipe.entityId and entity.entityId = topologyLinks.entityId;

But I get 0 results.
I posted my code here as well: [rafb.net]

I got this to work:
select * from entity, connects where entity.entityId = connects.aEndEntityId or entity.entityId = connects.zEndEntityId;


that will return 560 results. Now I need to also add it to join the toplogy table, and networkpipe table thats where i'm having issues.

Thanks