Page 1 of 1

select using join

Posted: Mon Aug 27, 2007 3:28 am
by shivam0101
This query is not returning any values if comp table is empty.

qtest_request: qtest_id(auto inc), member_id, test_id

comp: comp_id(auto_inc),member_id, score, test_id, ans_id, ans

Code: Select all

SELECT * FROM qtest_request, comp WHERE qtest_request.member_id=87 AND comp.survey_name_id !=qtest_request.test_id AND comp.member_id !=qtest_request.member_id

Posted: Mon Aug 27, 2007 7:15 am
by xpgeek
Rewrite it to use Left Join syntax:

Code: Select all

SELECT * FROM qtest_request q
LEFT JOIN comp c on (c.survey_name_id !=q.test_id  AND c.member_id !=q.member_id)
WHERE qtest_request.member_id=87
P.S. Anyway you query is strange, i think it can be rewritten better without != operators.