CODEWITHSUNDEEP
mysql
Anurag
Anurag

Reputation: 1013

Sub-Query in Mysql: Join on two table with string concatenation

I have question regarding sub query in MySQL. Problem is:-

There is two table

  1. userfirst
  2. userlast

describe userfirst

id firstname
1  Anurag
2  Abhishek
3  Prashant

describe userlast

src lastname
F/1 Jain
F/2 Singh
F/3 Sharma

Now I want output like:-

id firstname lastname
1  Anurag    Jain
2  Abhishek  Singh
3  Prashant  Sharma

I want to write only one query like:- 

select 
  f.id,
  f.firstname, 
  (select l.lastname from userlast l where l.src = 'F/'+f.id) as name 
from userfirst f

Is above query is possible, if yes/no Please tell the correct solution ?

Thanks

Upvotes: 1

Views: 2272

Answers (3)

Goutam Pal
Goutam Pal

Reputation: 1763

SELECT
    f.id,
    f.firstname,
    l.lastname
FROM
    userfirst f
INNER JOIN userlast l
    ON l.src = CONCAT('F/',f.id)

Upvotes: 1

Nikhil Kumar
Nikhil Kumar

Reputation: 134

In mysql there are many functions for string manipulation including concat you can use like 

mysql> SELECT CONCAT('My', 'S', 'QL');
        -> 'MySQL'

I guess you can use select f.id,f.firstname, (select l.lastname from userlast l where l.src = concat('F/',f.id) as name from userfirst f

Upvotes: 1

Stephan
Stephan

Reputation: 8090

Try using JOIN its much more efficient :

SELECT
    f.id,
    f.firstname,
    l.lastname
FROM
    userfirst f
LEFT JOIN userlast l
    ON l.src = CONCAT('F/',f.id)

You can see the result here , fiddle provided by Meherzad

Upvotes: 1

Related Questions