MySQL Forums
Forum List  »  PHP

Re: Too many results in my Query
Posted by: adam island
Date: November 12, 2012 12:45AM

if any user_info records matches, your query is returning ALL related records whether they have "keyword" in them or not. i think this is the reason that you are not getting the results that you are expecting.

i think you want to move items in your "where" section into the "on" sections

for example, instead of something like this

select * from tableA
LEFT JOIN tableB on tableB.id=tableA.bid
LEFT JOIN tableC on tableC.id=tableA.cid
where A.value like "%$str%" or B.value like "%$str%" or C.value like "%$str%"

it should be this, only join when there is a match, and then find if a match was found in the "where" section

select * from tableA
LEFT JOIN tableB on tableB.id=tableA.bid AND B.value like "%$str%"
LEFT JOIN tableC on tableC.id=tableA.cid AND C.value like "%$str%"
where A.value like "%$str%" or tableB.id is NOT NULL or tableC.id is not NULL

Options: ReplyQuote


Subject
Written By
Posted
November 09, 2012 02:09PM
Re: Too many results in my Query
November 12, 2012 12:45AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.