Anthony Martin
Anthony Martin

Reputation: 43

Selecting individual values from two tables while getting condition from a third

I have a MySQL query that is baffling me. I am trying to grab specific pieces of information from two different tables (blurbs and users), while restricting it to only people who are following.

I have the following query:

SELECT DISTINCT blurbs.text, blurbs.timestamp, users.name, 
     users.username, users.profilepic, users.id 
FROM blurbs,users 
LEFT OUTER JOIN follows 
ON blurbs.uid = follows.following AND follows.follower = ? 
WHERE (blurbs.uid = $user_id OR follows.following IS NOT NULL) 
     AND (LOWER(blurbs.text) LIKE '%$query%' OR LOWER(users.name) LIKE '%$query%') 
     AND blurbs.is_private=0 AND blurbs.uid=users.id 
LIMIT 0,30

It's not working properly, but I am getting overly confused because of the joins.

What should I do to remedy this?

Upvotes: 2

Views: 101

Answers (2)

Jagmag
Jagmag

Reputation: 10356

While I am not completely sure if this is exactly what you are looking for, let me explain what I have done based on my understanding of your question. Firstly, an INNER JOIN between blurbs and users instead of your cross join assuming that there will be a user for a blurb Also, since you said that you want to restrict it only to those who are following, i think you could possibly replace the LEFT OUTER with an INNER JOIN to help filter out the non-followers.

SELECT DISTINCT 
     blurbs.text, 
     blurbs.timestamp, 
     users.name, 
     users.username, 
     users.profilepic, 
     users.id 
FROM blurbs 
INNER JOIN users 
     ON blurbs.uid = users.id 
INNER JOIN follows 
     ON blurbs.uid = follows.following 
     AND follows.follower = users.id 
WHERE blurbs.uid = $user_id 
     AND (LOWER(blurbs.text) LIKE '%$query%' OR LOWER(users.name) LIKE '%$query%') 
     AND blurbs.is_private=0 
LIMIT 0,30

Upvotes: 1

Yogendra Singh
Yogendra Singh

Reputation: 34367

Try using all the join condition through where clause only e.g.

      select a.*, b.*     //<-- Selectign data from first two tables
      from table1 a, table2 b, table1 c
       where a.id = b.id         //<-- joining condition for first two tables
       and a.xxx= c.yyyyy        //some relation with third table
       and c.aaa =....           //filter conditions using third table
       and c.bbb =...            //filter conditions using third table

Trying to rephrase your query as below:

   SELECT DISTINCT blurbs.text, blurbs.timestamp, users.name, 
       users.username, users.profilepic, users.id 
   FROM blurbs,users, follows
   WHERE blurbs.uid=users.id
     AND blurbs.uid = follows.following 
     AND follows.follower = ?
     AND (blurbs.uid = $user_id OR follows.following IS NOT NULL) 
     AND (LOWER(blurbs.text) LIKE '%$query%' OR LOWER(users.name) LIKE '%$query%') 
     AND blurbs.is_private=0
   LIMIT 0,30

Having done this, I hope you can better control/manage the desired filter conditions.

Upvotes: 1

Related Questions