CODEWITHSUNDEEP
mysqlsqlmysql-workbench
user11100279
user11100279

Reputation:

How to use Concat() & Substring in one query?

For question purposes I will use minimal data for my examples.

I have a table called table1 and a column named test that looks like this:

test
5012

I am able to add an extra zero behind before the result of column test using this query:

SELECT CONCAT('0',test) as x from table1

this is the result of the query:

results table: table1

  x
05012

Now I have another table called table2 looking like this:

test    test2
05012     1

My question is, how do I join the two tables together based on that query above and concat the table1 with column test2 from table2? Making sure the first 4 characters of both columns test from both tables match together?

This is how table 1 should look like:

 Afterquery
  050121

Upvotes: 0

Views: 475

Answers (3)

slowko
slowko

Reputation: 875

Slightly different approach with a sub-query:

select concat(concat_test, test2) test_results 
  from 
    (select concat('0', test) concat_test from table1) table_alias 
  join 
    table2 on substring(concat_test,1,4) = substring(test,1,4);

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269593

I am curious why you wouldn't simply use table2?

select concat(t2.test, t2.test2) as afterquery
from table2 t2;

table1 doesn't seem to play a role.

If you want values in table2 filtered by table1, you can use exists:

select concat(t2.test, t2.test2) as afterquery
from table2 t2
where exists (select 1
              from table1 t1
              where t2.test = concat('0', t1.test)
            );

You can express this as a join:

select concat(t2.test, t2.test2) as afterquery
from table2 t2 join
     table1 t1
     on t2.test = concat('0', t1.test);

This is useful if you want columns from both tables -- but that is not necessary to answer the question. On the other hand, this runs the risk of duplication if there are multiple matches.

Upvotes: 1

kiks73
kiks73

Reputation: 3758

I think that this should be the solution. You need to use concat in the join between table1 and table2 

SELECT CONCAT('0', table1.test, table2.test2) AS Afterquery
FROM table1
INNER JOIN table2
ON CONCAT('0',table1.test) = table2.test

Upvotes: 1

Related Questions