CODEWITHSUNDEEP
sql
joshua
joshua

Reputation: 177

How to resolve subquery return more then one row

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 = "[email protected]"))

Upvotes: 2

Views: 71

Answers (5)

Mike Hanson
Mike Hanson

Reputation: 306

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 = "[email protected]"
                                  ORDER BY client_email, postJob_id)
ORDER BY freelancer_id) ---------------

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269803

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 = '[email protected]';

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.

Upvotes: 1

Murat Yıldız
Murat Yıldız

Reputation: 12032

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 = "[email protected]"

Upvotes: 2

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31993

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 = "[email protected]")
)

Upvotes: 2

Jim Macaulay
Jim Macaulay

Reputation: 5141

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 = "[email protected]"))

Upvotes: 2

Related Questions