Question 262453 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Join on one character out of two columns

I have following two tables which I would like to inner join based on part of each column content in both tables is equal to a value.
In below example I would like to inner join two tables where I join all lines which include "B" in the Group column.

------------------------
First table TABLE1: 
Group       Name
------------------------
A,B,C,D,E   CustomerNameA... 
G,E,D,L,P   CustomerNameB...
A,B,C,D,E   CustomerNameC... 
...

------------------------
Second table TABLE2
Group   Device
------------------------
A,Z     CustomerDeviceA...
A,B,C   CustomerDeviceB...
Z,P,O   CustomerDeviceC...
B,C,E   CustomerDeviceD...
...

I can not use

...
JOIN TABLE2
On TABLE1.Group like Concat('%', TABLE2.Group,'%')

because position of "B" in both TABLE1.Group and TABLE2.Group is not stable. "B" is somewhere in both columns.
I tried:

...
JOIN TABLE2
On (TABLE1.Group like '%B%' AND TABLE2.Group like '%B%')

but it returns different results as expected.

Tags:

2

Answers


  1. 0

    Your question lacks enough detail to provide a single, accurate answer.

    The queries below were written for SQL Server, but the concepts should translate easily to MySQL.

    We’ll start with the input:

    with TABLE1 as (
  select *
  from (
    values
      ('A,B,C,D,E', 'CustomerNameA')
    , ('G,E,D,L,P', 'CustomerNameB')
    , ('A,B,C,D,E', 'CustomerNameC')
  ) q ([Group], [Name])
), 
TABLE2 as (
  select *
  from (
    values 
      ('A,Z'  , 'CustomerDeviceA')
    , ('A,B,C', 'CustomerDeviceB')
    , ('Z,P,O', 'CustomerDeviceC')
    , ('B,C,E', 'CustomerDeviceD')
  ) q ([Group], [Device])
)

    This will give you all of the data, joined by "contains B" and "does not contain B":

    T1 as (
  select *
  , case when [Group] like '%B%' then 1 else 0 end as joincol
  from TABLE1
),
T2 AS (
  select *
  , case when [Group] like '%B%' then 1 else 0 end as joincol
  from TABLE2
)

select *
from T1
  inner join T2 ON T2.joincol = T1.joincol

    This will give you all of the data showing rows that are joined by "contains B" and other rows that don’t match.

    ,
T1 as (
  select *
  , case when [Group] like '%B%' then 1 end as joincol
  from TABLE1
),
T2 AS (
  select *
  , case when [Group] like '%B%' then 1 end as joincol
  from TABLE2
)

select *
from T1
  full outer join T2 ON T2.joincol = T1.joincol

    and this will give you only the rows that are joined by "contains B".

    ,
T1 as (
  select *
  , case when [Group] like '%B%' then 1 end as joincol
  from TABLE1
),
T2 AS (
  select *
  , case when [Group] like '%B%' then 1 end as joincol
  from TABLE2
)

select *
from T1
  inner join T2 ON T2.joincol = T1.joincol

    https://dbfiddle.uk/gANc7olQ

    Login or Signup to reply.
  2. 0

    "I can not use"

    JOIN TABLE2
On TABLE1.Group like Concat('%', TABLE2.Group,'%')

    Indeed. But this was the task:

    In below example I would like to inner join two tables where I join all lines which include "B" in the Group column.

    So you are interested in items that all have B. So, instead, you can use:

    JOIN TABLE2
ON CONCAT(',', TABLE1.Group, ',') LIKE '%B%' AND
   CONCAT(',', TABLE2.Group, ',') LIKE '%B%'

    The trick is that I prepend and append a comma to the value of Group so it will be true for the result of the CONCAT that any such field, B or anything else, if present in this result, then it will be between two commas, which handles the problem of variable substring length as well.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search