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

Mysql – SQL left join on column ignoring anything post (

I am trying to join 2 tables via 2 columns however, the left tables has entity values after them that I want to discard and they always start with (. Example: 1256390(1) or 23452(9), I need to disregard the (1) and (9)

Code Below

SELECT
A.Case_ID
A.Entity_ID
B.Entity_ID
B.Source
FROM Case_Table A
LEFT JOIN Source_Table B
ON A.Entity_ID = B.Entity_ID 
WHERE B.Source = 'Internal';

Nothing that I have tried works to this point.

Tags:

2

Answers


  1. 0 
    SELECT
A.Case_ID
A.Entity_ID
B.Entity_ID
B.Source
FROM Case_Table A
LEFT JOIN Source_Table B
ON SUBSTRING_INDEX(A.Entity_ID ,'(',1) = B.Entity_ID 
WHERE B.Source = 'Internal';
    Login or Signup to reply.
  2. 0 
    CREATE TABLE Case_Table
(
    Case_ID INT
  , Entity_ID INT
);

CREATE TABLE Source_Table
(
    Entity_id VARCHAR(15)
  , Source VARCHAR(15)
);

INSERT INTO Case_Table
VALUES
(12345, 67890)
, (123, 456);

INSERT INTO Source_Table
VALUES
('67890(1)', 'Internal')
, ('456(24)', 'Internal');

    SELECT A.Case_ID
     , A.Entity_ID
     , B.Entity_ID
     , B.Source
FROM Case_Table A
    LEFT JOIN Source_Table B
        ON A.Entity_ID = LEFT(B.Entity_ID, POSITION('(' IN B.Entity_ID)-1)
WHERE B.Source = 'Internal';
    Case_ID Entity_ID Entity_ID Source
    12345 67890 67890(1) Internal
    123 456 456(24) Internal 
    SELECT A.Case_ID
     , A.Entity_ID
     , B.Entity_ID
     , B.Source
FROM Case_Table A
    LEFT JOIN Source_Table B
        ON A.Entity_ID = SUBSTRING_INDEX(B.Entity_ID, '(', 1)
WHERE B.Source = 'Internal';
    Case_ID Entity_ID Entity_ID Source
    12345 67890 67890(1) Internal
    123 456 456(24) Internal

    fiddle

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

Back To Top
Search