Merging 2 different SQL results

I have 2 different tables in MySQL/MariaDB that have different data about computer systems. There is a common field for computer_name but the contents of that field can be different for the same computer. In TableA, the computer_name could be 'hostname' while in TableB the computer_name could be 'hostname@dom1.domain'. I need to see col1, col2, col3 from TableA and col5, col6, col7 from TableB where the computer_name is something LIKE '%hostname%'. Can this be done in pure SQL (selects or views or stored procedure) or can it be done in PHP?

TableA: +------------------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------------+------------------+------+-----+---------+----------------+ | tableA_id | int(10) unsigned | NO | PRI | NULL | auto_increment | | computer_name | varchar(50) | NO | | NULL | | | device_type | varchar(255) | NO | | NULL | | | ip_address | varchar(150) | NO | | NULL | | | os_type | varchar(50) | NO | | NULL | | | laptop | varchar(255) | NO | | NULL | | | operating_system | varchar(100) | NO | | NULL | | | last_report_time | varchar(100) | NO | | NULL | | | week_date | date | YES | | NULL | | | month_id | int(10) unsigned | NO | | NULL | | | year | int(10) unsigned | NO | | NULL | | | create_date | datetime | NO | | NULL | | +------------------+------------------+------+-----+---------+----------------+

TableB: +--------------------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------------+------------------+------+-----+---------+----------------+ | tableb_id | int(10) unsigned | NO | PRI | NULL | auto_increment | | computer_name | varchar(60) | NO | | NULL | | | state | varchar(30) | NO | | NULL | | | server_type | varchar(30) | NO | | NULL | | | os_name | varchar(60) | YES | | NULL | | | os_version | varchar(100) | YES | | NULL | | | environment_status | varchar(60) | YES | | NULL | | | mgmt_ip | text | YES | | NULL | | | ad_domain | text | YES | | NULL | | | owner | varchar(150) | YES | | NULL | | | organization | varchar(150) | YES | | NULL | | | dev_poc | varchar(255) | YES | | NULL | | | tech_poc | varchar(255) | YES | | NULL | | | application_desc | varchar(255) | YES | | NULL | | | criticality | varchar(30) | YES | | NULL | | | week_date | date | YES | | NULL | | | month_id | int(10) | NO | | NULL | | | year | int(10) | NO | | NULL | | | create_date | datetime | NO | | NULL | | +--------------------+------------------+------+-----+---------+----------------+

Select from TableA; || computer_name || ip_address || os_type || operating_system || last_report_time || create_date || || listserv-test || www.xxx.yyy.zzz || Unknown - Linux Red Hat Enterprise Server 7.9 || Linux Red Hat Enterprise Server 7.9 (3.10.0-1160.24.1.el7.x86_64) || Mon, 19 Apr 2021 10:51:23 +0000 || 2021-04-20 08:41:05 || || listserv-test || www.xxx.yyy.zzz || Unknown - Linux Red Hat Enterprise Server 7.9 || Linux Red Hat Enterprise Server 7.9 (3.10.0-1160.21.1.el7.x86_64) || Fri, 09 Apr 2021 13:31:38 +0000 || 2021-04-12 14:55:37 || || listserv-test || www.xxx.yyy.zzz || Unknown - Linux Amazon Linux 2 || Linux Amazon Linux 2 (4.14.225-169.362.amzn2.x86_64) || Mon, 05 Apr 2021 10:58:18 +0000 || 2021-04-08 13:53:14 || || listserv.yyy.zzz || www.xxx.yyy.zzz || Unknown - Linux CentOS 6.10 || Linux CentOS 6.10 (2.6.32-754.35.1.el6.x86_64) || Mon, 19 Apr 2021 10:44:05 +0000 || 2021-04-20 08:41:05 || || listserv.yyy.zzz || www.xxx.yyy.zzz || Unknown - Linux CentOS 6.10 || Linux CentOS 6.10 (2.6.32-754.35.1.el6.x86_64) || Mon, 12 Apr 2021 10:44:52 +0000 || 2021-04-12 14:55:37 || || listserv.yyy.zzz || www.xxx.yyy.zzz || Unknown - Linux CentOS 6.10 || Linux CentOS 6.10 (2.6.32-754.35.1.el6.x86_64) || Mon, 05 Apr 2021 10:59:32 +0000 || 2021-04-08 13:53:14 ||

Select from TableB || computer_name || state || os_name || os_version || owner || organization || dev_poc || tech_poc || application_desc || || AVALISTSERVMSGPR02 || Installed || || Linux Amazon Linux 2 (4.14.219-161.340.amzn2.x86_64) || || || || || || || LISTSERV || Installed || Linux CentOS || Linux CentOS 6.10 (2.6.32-754.11.1.el6.x86_64) || || || || || Messaging || || LISTSERV.yyy.zzz || Installed || Linux CentOS || Linux CentOS 6.10 (2.6.32-754.18.2.el6.x86_64) || || || || || ||

1 answer

  • answered 2021-04-21 15:03 Gordon Linoff

    You could use a join like this:

    select . . .
    from a join
         b
         on a.computer_name = substring_index(b.computer_name, '@', 1);