I have a table defining 'connections' and another defining 'equipment'
我有一个表定义'连接'的表和另一个定义'设备'的表
I have a 'source_system_name' and 'destination_system_name'
columns in connections table.
and "system_name"
that matches these in "equipment"
table.
我在连接表中有一个'source_system_name'和'destination_system_name'列。和“system_name”匹配“设备”表中的这些。
The equipment can be various type:
设备可以是各种类型:
1. Core
2. Aggregation
3. Customer.
I am trying to define a query to return the "type" for both the source and destinations fields from equipment table.
我正在尝试定义一个查询,以便从设备表中返回源和目标字段的“类型”。
connection table is like:
连接表如下:
id | Source Name | Source port | Destination Name | Destination Port | etc...
-----------------------------------------------------------------------------
4 Device_core1 1/1 Device_agg3 3/4
Equipment table is like:
设备表如下:
id | Equip_name | Equip type | etc....
------------------------------------------
3 Device_core1 | Core
7 Device_agg3 | Aggregation
I am trying to get the results returned like:
我试图让结果返回如下:
id | Source Name |Source port|Source type|Destination Name|Destination Port|Destination type|
---------------------------------------------------------------------------------------------
1 Device_core1 1/1 Core Device_agg3 3/4 Aggregation
The query I'm trying to use is:
我正在尝试使用的查询是:
SELECT DISTINCT * FROM connections
LEFT JOIN equipment on connections.system_name_source=equipment.system_name
OR connections.system_name_dest=equipment.system_name;
When I do this I get double records where the, Source type
field gets overwritten by Destination type
and vice versa. Is there a query I can use to lookup the equipment
table for both source and destination data? ie.,
当我这样做时,我得到双记录,其中,源类型字段被目标类型覆盖,反之亦然。是否有查询可用于查找设备表中的源数据和目标数据?即,
id | Source Name |Source port|Source type|Destination Name|Destination Port|Destination type|
1 Device_core1 1/1 Aggregation Device_agg3 3/4 Aggregation
2 Device_core1 1/1 Core Device_agg3 3/4 Core
2 个解决方案
#1
2
You will need to do Self Join.. Try this query:
你需要做自我加入..试试这个查询:
SELECT
connections.id,
connections.Source_Name,
connections.Source_port,
src.systype SourceType,
connections.Destination_Name,
connections.Destination_Port,
tgt.systype DestinationType
FROM connections
LEFT JOIN equipment src on connections.system_name_source=src.system_name
LEFT JOIN equipment tgt on connections.system_name_dest=tgt.system_name
In the above query, I have aliased the equipment
table twice as src
and tgt
.
在上面的查询中,我将设备表别名为src和tgt。
#2
1
Try this:
SELECT c.id, c.sourceName, c.sourcePort,
MAX(IF(c.sourceName = e.Equip_name, e.equipType, '')) sourceType,
c.destinationName, c.destinationPort,
MAX(IF(c.destinationName = e.Equip_name, e.equipType, '')) destinationType
FROM `connection` c
LEFT JOIN equipment e ON e.Equip_name IN (c.sourceName, c.destinationName)
GROUP BY c.id;
#1
2
You will need to do Self Join.. Try this query:
你需要做自我加入..试试这个查询:
SELECT
connections.id,
connections.Source_Name,
connections.Source_port,
src.systype SourceType,
connections.Destination_Name,
connections.Destination_Port,
tgt.systype DestinationType
FROM connections
LEFT JOIN equipment src on connections.system_name_source=src.system_name
LEFT JOIN equipment tgt on connections.system_name_dest=tgt.system_name
In the above query, I have aliased the equipment
table twice as src
and tgt
.
在上面的查询中,我将设备表别名为src和tgt。
#2
1
Try this:
SELECT c.id, c.sourceName, c.sourcePort,
MAX(IF(c.sourceName = e.Equip_name, e.equipType, '')) sourceType,
c.destinationName, c.destinationPort,
MAX(IF(c.destinationName = e.Equip_name, e.equipType, '')) destinationType
FROM `connection` c
LEFT JOIN equipment e ON e.Equip_name IN (c.sourceName, c.destinationName)
GROUP BY c.id;