CODEWITHSUNDEEP
mysqlsql
mKagac
mKagac

Reputation: 31

Get result from two table

I am trying to get result from two tables in one query, but the problem is I need columns from both the tables in my where clause- Example

SELECT activity.*,
    (
      SELECT photos 
      FROM posts 
      WHERE activity.content_id = posts.record_number
    ) as p 
FROM activity 
WHERE activity.liked_post > 0 OR activity.comments > 0 OR p > 0 
ORDER BY date DESC 
LIMIT 0,10

This example does not work. I have no idea how to make this query work. If p = 0 the record should be skipped but content_id can be 0 or any number, if content_id > 0 then need to check photos in posts table, if content_id = 0 we must skip.

So photos in posts table have value 0 or 1.

content_id in activity table is just same ID/record_number from posts

Maybe a little confusing question but don't know how to explain

Upvotes: 0

Views: 56

Answers (2)

mKagac
mKagac

Reputation: 31

Found solution

SELECT a.*,
       p.photos
FROM activity a
LEFT JOIN content p ON a.content_id = p.record_number
WHERE (a.friend_with > 0
       OR a.liked_post > 0
       OR a.comments > 0)
  OR (a.content_id = p.record_number
      AND photos > 0)
ORDER BY date DESC
LIMIT 0,10

Upvotes: 0

Sloan Thrasher
Sloan Thrasher

Reputation: 5040

Rather than a sub-select for the photo, use a join. The p > 0 in your query is taken care of by using a join. Only rows that have photos will be included in the results.

This should return the rows you expect:

SELECT a.*,
    p.photos
FROM activity a
LEFT JOIN posts p
    ON a.content_id = p.record_number
WHERE (a.liked_post > 0 OR a.comments > 0)
    AND (a.content_id = p.record_number AND content_id > 0)
        OR liked_post > 0)
ORDER BY date DESC 
LIMIT 0,10;

Note: I aliased the table names to reduce the amount of typing needed.

Upvotes: 1

Related Questions