Unknown column, any ideas? syntax error.
Hello everyone! having alittle issues here...
Code:
mysql> SELECT
-> ncim.entity.entityId, ncim.entity.mainNodeEntityId,
-> ncim.entity.entityName, ncim.entity.domainMgrId,
-> ncim.entity.entityType, ncim.entity.displayLabel,
-> ncim.entity.alias,
-> ospfService.routerId, ospfService.entityId,
-> ospfService.isAreaBdrRtr,ospfService.isAsBdrRtr, ospfService.isDrRtr,
-> ospfService.isBdrRtr, ospfService.isDrOtherRtr,
-> ncim.hostedService.hostingEntityId, hostedService.hostedEntityId,
-> ncim.ospfRoutingDomain.ospfDomain, ncim.ospfRoutingDomain.entityId
->
-> FROM ospfRoutingDomain
->
-> INNER JOIN collects ON collects.collectingEntityId = ospfRoutingDomain.entityId
-> INNER JOIN entity ON ncim.entity.entityId = collects.collectedEntityId;
ERROR 1054 (42S22): Unknown column 'ospfService.routerId' in 'field list'
mysql> describe ospfService;
+--------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| entityId | int(11) | NO | PRI | | |
| routerId | varchar(15) | NO | | | |
| isAreaBdrRtr | tinyint(4) | YES | | NULL | |
| isAsBdrRtr | tinyint(4) | YES | | NULL | |
| isDrRtr | tinyint(4) | YES | | NULL | |
| isBdrRtr | tinyint(4) | YES | | NULL | |
| isDrOtherRtr | tinyint(4) | YES | | NULL | |
+--------------+-------------+------+-----+---------+-------+
7 rows in set (0.00 sec)
Anyone know why I'm getting this error? As you can see its in the table...
Im trying to mimic somthing like this (which works):
Code:
[1] SELECT s.network AS ’Network’,
[2] s.netmask AS ’Netmask’,
[3] e.entityName AS ’Entity Name’
[4] FROM subnet s
[5] INNER JOIN collects c ON c.collectingEntityId = s.entityId
[6] INNER JOIN entity e ON e.entityId = c.collectedEntityId
[7] ORDER BY s.network
ospfRoutingDomain is also a collection object so
INNER JOIN collects c ON c.collectingEntityId = s.entityId
should also work with ospfRoutingDomain rather than s.entityId 9subnet).
Any help would be great! :D
Re: Unknown column, any ideas? syntax error.
You cannot select columns from a table that is not referenced. You need to join the table, then you will be able to select from it. It's easier to see if you use aliases, because the aliases you have not accounted for are the tables you have to join in:
Code:
SELECT
e.entityId, e.mainNodeEntityId,
e.entityName, e.domainMgrId,
e.entityType, e.displayLabel,
e.alias,
os.routerId, os.entityId,
os.isAreaBdrRtr,os.isAsBdrRtr, os.isDrRtr,
os.isBdrRtr, os.isDrOtherRtr,
hs.hostingEntityId, hs.hostedEntityId,
ord.ospfDomain, ord.entityId
FROM ospfRoutingDomain ord
INNER JOIN collects c
ON c.collectingEntityId = ord.entityId
INNER JOIN entity e
ON e.entityId = c.collectedEntityId;
Currently missing are ospfService (os) and hostedService (hs). Another reason to use aliases is that you don't have to specify database name more than once. I noticed you used database names inconsistently for some columns: ncim.hostedService.hostingEntityId, hostedService.hostedEntityId (One is using ncim database, while the other one is using current database - this could cause unexpected behavior if missed).
Re: Unknown column, any ideas? syntax error.
Thanks for the help again,
I do like your method alot better it seems cleaner as well.
I'm not sure if I'm logically connecting these tables correctly or not...
This is what I think all the inner joins should be...
Code:
INNER JOIN collects c
ON e.entityId = c.collectedEntityId
INNER JOIN collects c
ON c.collectingEntityId = ord.entityId
INNER JOIN ospfRoutingDomain ord
ON ord.entityId = e.entityId
INNER JOIN hostedService hs
ON hs.hostingEntityId = e.entityId
INNER JOIN ospfService os
ON os.entityId = hs.hostedEntityId;
based on these tables (you can see the foreign keys).
Code:
mysql> show create table entity;
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| entity | 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 |
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> show create table collects;
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| collects | CREATE TABLE `collects` (
`collectingEntityId` int(11) NOT NULL,
`collectedEntityId` int(11) NOT NULL,
`sequence` int(11) default NULL,
PRIMARY KEY (`collectingEntityId`,`collectedEntityId`),
KEY `collects_idx` (`collectedEntityId`,`collectingEntityId`),
CONSTRAINT `collects_entity_fk1` FOREIGN KEY (`collectingEntityId`) REFERENCES `entity` (`entityId`) ON DELETE CASCADE,
CONSTRAINT `collects_entity_fk2` FOREIGN KEY (`collectedEntityId`) REFERENCES `entity` (`entityId`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin |
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> show create table ospfRoutingDomain;
+-------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ospfRoutingDomain | CREATE TABLE `ospfRoutingDomain` (
`entityId` int(11) NOT NULL,
`ospfDomain` int(11) NOT NULL,
PRIMARY KEY (`entityId`),
CONSTRAINT `ospfRoutingDomain_entity_fk` FOREIGN KEY (`entityId`) REFERENCES `entity` (`entityId`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin |
+-------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> show create table hostedService;
+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| hostedService | CREATE TABLE `hostedService` (
`hostingEntityId` int(11) NOT NULL,
`hostedEntityId` int(11) NOT NULL,
PRIMARY KEY (`hostingEntityId`,`hostedEntityId`),
KEY `hostedService_idx` (`hostedEntityId`,`hostingEntityId`),
CONSTRAINT `hostedService_entity_fk1` FOREIGN KEY (`hostingEntityId`) REFERENCES `entity` (`entityId`) ON DELETE CASCADE,
CONSTRAINT `hostedService_entity_fk2` FOREIGN KEY (`hostedEntityId`) REFERENCES `entity` (`entityId`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin |
+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> show create table ospfService;
+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ospfService | CREATE TABLE `ospfService` (
`entityId` int(11) NOT NULL,
`routerId` varchar(15) collate utf8_bin NOT NULL,
`isAreaBdrRtr` tinyint(4) default NULL,
`isAsBdrRtr` tinyint(4) default NULL,
`isDrRtr` tinyint(4) default NULL,
`isBdrRtr` tinyint(4) default NULL,
`isDrOtherRtr` tinyint(4) default NULL,
PRIMARY KEY (`entityId`),
CONSTRAINT `ospfService_entity_fk` FOREIGN KEY (`entityId`) REFERENCES `entity` (`entityId`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin |
+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
But I get an empty set when I run this....Do you see if i'm not connecting these tables properly or perhaps connecting too many at once?
Code:
SELECT
e.entityId, e.mainNodeEntityId,
e.entityName, e.domainMgrId,
e.entityType, e.displayLabel,
e.alias,
os.routerId, os.entityId,
os.isAreaBdrRtr,os.isAsBdrRtr, os.isDrRtr,
os.isBdrRtr, os.isDrOtherRtr,
hs.hostingEntityId, hs.hostedEntityId,
ord.ospfDomain, ord.entityId
FROM entity e
INNER JOIN hostedService hs
ON hs.hostingEntityId = e.entityId
INNER JOIN ospfService os
ON os.entityId = hs.hostedEntityId
INNER JOIN ospfRoutingDomain ord
ON ord.entityId = e.entityId
INNER JOIN collects c
ON c.collectingEntityId = ord.entityId;
I wasn't sure where to start the FROM clause...I used entity because it contains the most data and everything stems from entity.
Re: Unknown column, any ideas? syntax error.
I couldn't see any immediate errors in your query. Make sure that you're using all the right columns. You have columns hs.hostedEntityId and hs.hostingEntityId, maybe there was a mix-up there?
If you are joining a table where there might not be a row for the main table, using INNER JOIN will not return that row. You would need to use LEFT JOIN for those tables, where the columns for the table that has no corresponding row will be NULL (even if they normally don't allow NULL).
