How to resolve subquery return more then one row

user_13552946 0
Viewed 64

i have three tables in database in which the first table consist of freelancer section second table of client and 3 in which when client selects an freelancer the 3 table store id of freelancer and id of client. but when i want to retreive the information of freelancer selected by client it shows the error mentioned above in heading here is my query

SELECT * FROM `freelancer_detail` 
WHERE f_id = (select freelancer_id 
from shortlisted_freelancers 
where shortlisted_id =(SELECT postJob_id from client_detail 
WHERE client_email = "joshua@gmail.com"))
sql
5 Answers

Please use IN clause in case you have multiple values in the other table,

SELECT * FROM `freelancer_detail` 
WHERE f_id in (select freelancer_id 
from shortlisted_freelancers 
where shortlisted_id  in (SELECT postJob_id from client_detail 
WHERE client_email = "joshua@gmail.com"))
user_44736150

use in

SELECT f.* FROM `freelancer_detail` f
WHERE f_id in (select freelancer_id 
from shortlisted_freelancers 
where shortlisted_id in (SELECT postJob_id from client_detail 
WHERE client_email = "joshua@gmail.com")
)
user_68085830

Try this:

SELECT * FROM freelancer_detail fd 
    LEFT JOIN shortlisted_freelancers sf ON fd.f_id = sf.freelancer_id
    LEFT JOIN postJob_id pj ON sf.shortlisted_id = pj.postJob_id
WHERE fd.client_email = "joshua@gmail.com"
user_16040480

Whilst all of the answers submitted so far look better than the OP code they don't actually provide the answer to the original question how do you get a subquery to return only a single row.

One option is to use SELECT TOP 1, but it depends on you including an ORDER BY clause that ensures the top row in the resul set is the one you want.

I am not advocating you actually do this, (I think a join like that suggested by Murat is much better) something like the following would make sure the subqueries only return a single row.

SELECT * FROM `freelancer_detail` 
WHERE f_id = (SELECT TOP 1 freelancer_id 
          FROM shortlisted_freelancers 
          WHERE shortlisted_id = (SELECT TOP 1 postJob_id
                                  FROM client_detail 
                                  WHERE client_email = "joshua@gmail.com"
                                  ORDER BY client_email, postJob_id)
ORDER BY freelancer_id) ---------------
user_57847120

Use join:

SELECT fd.*
FROM freelancer_detail fd JOIN
     shortlisted_freelancers sf
     ON fd.f_id = sf.freelancer_id JOIN
     client_detail cd
     ON sf.shortlisted_id = cd.postJob_id 
WHERE cd.client_email = 'joshua@gmail.com';

Notes:

  • Always qualify all column references, especially when your query refers to multiple table.
  • Use table aliases, so the query is easier to write and to read.
  • The standard SQL delimiter for strings is the single quote, not the double quote.

You should also know that joins usually optimize better than ins.

user_11440350
Related