CODEWITHSUNDEEP
mysqlsqlselectgroup-bymax
Nigel
Nigel

Reputation: 145

join two columns to one column

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.

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

Upvotes: 2

Views: 114

Answers (2)

hashbrown
hashbrown

Reputation: 3516

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.

Upvotes: 2

Saharsh Shah
Saharsh Shah

Reputation: 29051

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;

Upvotes: 1

Related Questions