0

Lets say i have the following two tables:

Table1:

|IP    |Name  | Gateway | Version |
+------+------+---------+---------+
|1     |A     | 1.0.0   | v1      |
+------+------+---------+---------+
|2     |B     | 2.0.0   | v2      |
+------+------+---------+---------+
|3     |C     | 3.0.0   | v3      |   
+------+------+---------+---------+
|4     |D     | 4.0.0   | v4      |
+------+------+---------+---------+

Table2:

|IP    |Name  | Gateway | Version |
+------+------+---------+---------+
|1     |A     | 1.0.0   | v1      |
+------+------+---------+---------+
|2     |F     | 2.0.0   | v2      |
+------+------+---------+---------+
|3     |C     | 3.0.1   | v3      |   
+------+------+---------+---------+
|4     |D     | 4.0.0   | v10      |
+------+------+---------+---------+

How would i write a query to return a list of records from both tables that have certain different values based on the primary key (Lets use IP in this example)?

For example, if i chose to compare only Version and Name, the result would be:

|IP    |Name  | Gateway | Version |
+------+------+---------+---------+
|2     |B     | 2.0.0   | v2      |
+------+------+---------+---------+
|2     |F     | 2.0.0   | v2      |
+------+------+---------+---------+
|4     |D     | 4.0.0   | v4      |
+------+------+---------+---------+
|4     |D     | 4.0.0   | v10     |

Thank you!

closed as off-topic by mustaccio, Jon of All Trades, Marcello Miorelli, John Eisbrener, kevinsky Jun 20 at 13:24

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – mustaccio, John Eisbrener, kevinsky
If this question can be reworded to fit the rules in the help center, please edit the question.

  • 1
    Are you asking how to join tables? How to compare two columns? Something else? – mustaccio Jun 19 at 16:51
  • 2
    Either your sample output is flawed, or you description ("compare only ...") is flawed. – Rick James Jun 19 at 18:25
  • Could you please provide your table structures as DDL (CREATE TABLE blah...) and your data as DML (INSERT INTO blah VALUES (...)). Help us to help you! – Vérace Jun 20 at 4:22
  • "Either your sample output is flawed, or you description ("compare only ...") is flawed" @RickJames yes it is not complety clear atleast it as some contradictions, i also had to read the question "300" times to understand how the expected result "worked".. – Raymond Nijland Jun 20 at 11:12
  • "Could you please provide your table structures as DDL" @Vérace see db-fiddle.com those kind off websites most of the time support importing from ascii based table output.. See blue button at the bottom "TEXT TO DDL" easy and fast.. – Raymond Nijland Jun 20 at 11:15
2

Note MySQL 8 is has been released for more then a year now which supports COUNT(DISTINCT ...) OVER(PARTITION BY ...) syntax..

There are multiple solutions possible,

Yes the solutions are hard, one of the problems is that MySQL does not (jet) support

COUNT(DISTINCT ...) OVER(PARTITION BY ...)

otherwise the solution would have been much more easy.

one of the solutions is.

Query

SELECT 
 *
FROM (

  SELECT 
   table1.*
  FROM 
   table1 

  UNION ALL 

  SELECT 
   table2.*
  FROM 
   table2 
) AS merged

INNER JOIN (

  SELECT 
     total_data.IP 
   , COUNT(*) = COUNT(DISTINCT total_data.Name) AS has_name_changes
   , COUNT(*) = COUNT(DISTINCT total_data.Gateway) AS has_gateway_changes
   , COUNT(*) = COUNT(DISTINCT total_data.Version) AS has_version_changes
  FROM (
    SELECT 
     table1.*
    FROM 
     table1 

    UNION ALL 

    SELECT 
     table2.*
    FROM 
     table2 
  ) AS total_data
  GROUP BY 
     total_data.IP  
) AS search_filter
ON
   merged.IP = search_filter.IP
WHERE
   search_filter.has_name_changes = 1
 OR
   search_filter.has_version_changes = 1

Result

| IP  | Name | Gateway | Version | IP  | has_name_changes | has_gateway_changes | has_version_changes |
| --- | ---- | ------- | ------- | --- | ---------------- | ------------------- | ------------------- |
| 2   | B    | 2.0.0   | v2      | 2   | 1                | 0                   | 0                   |
| 2   | F    | 2.0.0   | v2      | 2   | 1                | 0                   | 0                   |
| 4   | D    | 4.0.0   | v4      | 4   | 0                | 0                   | 1                   |
| 4   | D    | 4.0.0   | v10     | 4   | 0                | 0                   | 1                   |

see demo

The other solution which is even harder and only works when you have two records, one in each table.

Query

SET SESSION group_concat_max_len = @@max_allowed_packet;

SELECT 
   merged.IP 
 , SUBSTRING_INDEX(
     SUBSTRING_INDEX(
       SUBSTRING_INDEX(
         SUBSTRING_INDEX(
           GROUP_CONCAT(
             CONCAT_WS(
                ','
              , merged.Name
              , merged.Gateway
              , merged.Version
             )
             SEPARATOR ';'
           )
            , ';'
            , sql_number_generator.number
         )
         , ';'
         , -1
      )
      , ','
      , 1
    )
    , ','
    , -1
  ) AS Name
 , SUBSTRING_INDEX(
     SUBSTRING_INDEX(
       SUBSTRING_INDEX(
         SUBSTRING_INDEX(
           GROUP_CONCAT(
             CONCAT_WS(
                ','
              , merged.Name
              , merged.Gateway
              , merged.Version
             )
             SEPARATOR ';'
           )
            , ';'
            , sql_number_generator.number
         )
         , ';'
         , -1
      )
      , ','
      , 2
    )
    , ','
    , -1
  ) AS Gateway
 , SUBSTRING_INDEX(
     SUBSTRING_INDEX(
       SUBSTRING_INDEX(
         SUBSTRING_INDEX(
           GROUP_CONCAT(
             CONCAT_WS(
                ','
              , merged.Name
              , merged.Gateway
              , merged.Version
             )
             SEPARATOR ';'
           )
            , ';'
            , sql_number_generator.number
         )
         , ';'
         , -1
      )
      , ','
      , 3
    )
    , ','
    , -1
  ) AS Version


,  SUBSTRING_INDEX(
         SUBSTRING_INDEX(
           GROUP_CONCAT(
             CONCAT_WS(
                ','
              , merged.Name
              , merged.Gateway
              , merged.Version
             )
             SEPARATOR ';'
           )
            , ';'
            , sql_number_generator.number
         )
         , ';'
         , -1
      ) AS merged_record
FROM (
  SELECT 
   *
  FROM 
   table1 
  UNION ALL
  SELECT 
   *
  FROM 
    table2 
) AS merged
CROSS JOIN (
  SELECT 
   1 AS number 
  UNION
  SELECT
   2 AS number  
) AS sql_number_generator 
GROUP BY 
   merged.IP 
 , sql_number_generator.number
HAVING 
   MIN(merged.Name) <> MAX(merged.Name)
 OR
   MIN(merged.Version) <> MAX(merged.Version)

Result

| IP  | Name | Gateway | Version | merged_record |
| --- | ---- | ------- | ------- | ------------- |
| 2   | B    | 2.0.0   | v2      | B,2.0.0,v2    |
| 2   | F    | 2.0.0   | v2      | F,2.0.0,v2    |
| 4   | D    | 4.0.0   | v4      | D,4.0.0,v4    |
| 4   | D    | 4.0.0   | v10     | D,4.0.0,v10   |

see demo

Not the answer you're looking for? Browse other questions tagged or ask your own question.