CODEWITHSUNDEEP
mysqlsqljoin
chris
chris

Reputation: 36947

mysql multiple inner join complication

I have this query. Which seems to be giving me complications. It works if I take out the 3rd tables constraint. But I need that 3rd tables contraint to refine a search I am working on. My problem is its not giving me an error but its not yielding results either when it should yield at least 1 with the data I have in my DB.

SELECT loc.*, 
       a.firstname, 
       a.lastname, 
       a.profileimg,
       (((acos(sin((37.2790669*pi()/180)) * sin((`latitude`*pi()/180))+cos((37.2790669*pi()/180)) * cos((`latitude`*pi()/180)) * cos(((-121.874722 - `longitude`)*pi()/180))))*180/pi())*60*1.1515) AS `distance` 
  FROM geo_locations loc
  JOIN memb_Info a ON a.mID = loc.mID
 WHERE a.firstname LIKE 'Lisa%' 
   AND loc.primaryAddress = '1' 
   AND memb_x.xName LIKE '%Monkey'
ORDER BY `distance` ASC

if I remove 

AND memb_x.xName LIKE '%Monkey'

I get results no problem but the memb_x table has a column xName that is a filter of sorts of which I want to apply to the query above but I seem to be lost in how I should handle that. I tried to inner join properly but that didn't do me any benifit, that resulted in duplicate results and results that didn't actuall cover what I what I was trying to achieve.

So break down of it. What I want to do is do a radial search for my members so they can see other members in there area.. That would initially stem from the geo_locations with that I am trying to get the information from the id's returned so I can INNER JOIN the memb_Info table for the users firstname lastname, etc.. the one connector between all the tables is the mID which is the member id. Now this member ID also on the third table has users who have similar interests or filters so I want to thin my results down based on those filters this is where I am not constructing my query properly, and need help fixing it.

Upvotes: 0

Views: 427

Answers (1)

jasonjifly
jasonjifly

Reputation: 347

I'm not sure what do you mean, but is it like this which you need:

SELECT loc.*, 
   a.firstname, 
   a.lastname, 
   a.profileimg,
   (((acos(sin((37.2790669*pi()/180)) * sin((`latitude`*pi()/180))+cos((37.2790669*pi()/180)) * cos((`latitude`*pi()/180)) * cos(((-121.874722 - `longitude`)*pi()/180))))*180/pi())*60*1.1515) AS `distance` 
FROM geo_locations loc
JOIN memb_Info a ON a.mID = loc.mID 
JOIN memb_x x ON x.mID=loc.mID
WHERE a.firstname LIKE 'Lisa%' 
AND loc.primaryAddress = '1' 
AND x.xName LIKE '%Monkey'
ORDER BY `distance` ASC

Upvotes: 1

Related Questions