CODEWITHSUNDEEP
mysql
Ramalingam Perumal
Ramalingam Perumal

Reputation: 1427

Subquery return always single record

I am try to merge the query 1 is sub query of query 2 in 3. But it will return always single record. Below is my Query,

1.SELECT REPLACE(LEFT(friend_id, LENGTH(friend_id)-2),'["','') AS friend_id FROM `friends_list` WHERE login_userid=90 

Output :
friend_id   
32,44

2.SELECT id, CONCAT(firstname," ",lastname) AS username FROM register WHERE id IN(32,44)

Output :
id  username    
32  Suresh M
44  Senthil Kumar

Sample code,

3.SELECT t1.id, CONCAT(t1.firstname," ",t1.lastname) AS username 
FROM register AS t1 
INNER JOIN friends_list AS t2 ON t1.id=t2.login_userid
WHERE t1.id IN( SELECT REPLACE(LEFT(friend_id, LENGTH(friend_id)-2),'["','') AS friend_id FROM `friends_list` WHERE login_userid=90 )

Output :
id  username    
32  Suresh M

I want to the result,

id  username    
32  Suresh M
44  Senthil Kumar

Please Correct my bad query(3rd).

Upvotes: 2

Views: 87

Answers (4)

Sagar Naliyapara
Sagar Naliyapara

Reputation: 4161

This should work foy you @RamaLingam

Try this without using INNER JOIN,

SELECT t1.id
    , CONCAT(t1.firstname, " ", t1.lastname) AS username
    FROM register t1
    WHERE t1.id IN (
        SELECT CAST(REPLACE(LEFT(t2.friend_id, LENGTH(t2.friend_id)-2),'["','') AS UNSIGNED)
            FROM friend_list t2
            WHERE t2.login_userid = 90
    )

Upvotes: 1

Shailesh Katarmal
Shailesh Katarmal

Reputation: 2785

SELECT id, CONCAT(firstname," ",lastname) AS username 
FROM register 
WHERE id IN( SELECT (select TRIM(BOTH '["' from (select TRIM(BOTH '"]' from friend_id)))) as friend_id  FROM `friends_list` WHERE login_userid=90 )

Upvotes: 2

Avi
Avi

Reputation: 1145

Just remove your inner join condition 

SELECT t1.id, CONCAT(t1.firstname," ",t1.lastname) AS username 
    FROM register AS t1 
    WHERE t1.id IN( SELECT REPLACE(LEFT(friend_id,      LENGTH(friend_id)-2),'["','') AS friend_id FROM `friends_list` WHERE login_userid=90 )

Upvotes: 2

KaeL
KaeL

Reputation: 3659

Can you try this one:

SELECT R.id
    , CONCAT(R.firstname, " ", R.lastname) AS username
    FROM register R
    WHERE R.id IN (
        SELECT CAST(REPLACE(LEFT(F.friend_id, LENGTH(F.friend_id)-2) ,'["' ,'') AS UNSIGNED)
            FROM friend_list F
            WHERE F.login_userid = 90
    )

Upvotes: -1

Related Questions