select using join

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
shivam0101
Forum Contributor
Posts: 197
Joined: Sat Jun 09, 2007 12:09 am

select using join

Post 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
User avatar
xpgeek
Forum Contributor
Posts: 146
Joined: Mon May 22, 2006 1:45 am
Location: Kyiv, Ukraine
Contact:

Post 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.
Post Reply