CODEWITHSUNDEEP
mysqljoin
Muji Yanto
Muji Yanto

Reputation: 21

How to select where left join in mysql

I have query like this 

SELECT tb_post.*, 
      (SELECT GROUP_CONCAT(DISTINCT tb_category.category_name) 
       FROM tb_category 
       LEFT JOIN tb_terms ON tb_terms.parent_1 = tb_category.category_id 
       WHERE tb_terms.post_id = `tb_post`.post_id and category_type="post" 
       GROUP BY tb_terms.post_id) AS parent1 
FROM (`tb_post`)

enter image description here

But this where is not run. This is always return all row in table.. I want add where why?

I do not want to change the shape query and i I need all the data from tb_post but where tb_category.category_type=$value and do not change the structure of the query

Upvotes: 1

Views: 203

Answers (3)

Kickstart
Kickstart

Reputation: 21513

The best solution would be to modify your query as @Rimas suggests, but with the fragment of the function to generate that query I can't see enough to see how to do this.

If that is not possible then I would be tempted to somewhat abuse the HAVING clause and do something like this:-

SELECT tb_post.*, 
      (SELECT GROUP_CONCAT(DISTINCT tb_category.category_name) 
       FROM tb_category 
       INNER JOIN tb_terms ON tb_terms.parent_1 = tb_category.category_id 
       WHERE tb_terms.post_id = `tb_post`.post_id 
       AND category_type="post" 
       GROUP BY tb_terms.post_id) AS parent1 
FROM (`tb_post`)
HAVING parent1 IS NOT NULL

Upvotes: 0

Rimas
Rimas

Reputation: 6024

Use INNER JOIN:

SELECT tb_post.*, tc.categories
FROM tb_post
INNER JOIN (
  SELECT tb_terms.post_id, GROUP_CONCAT(DISTINCT tb_category.category_name) AS categories
  FROM tb_category 
  LEFT JOIN tb_terms ON tb_terms.parent_1 = tb_category.category_id 
  WHERE tb_category.category_type="post" 
  GROUP BY tb_terms.post_id
) tc ON tc.post_id = tb_post.post_id

Upvotes: 0

Affan
Affan

Reputation: 1140

if u not need tb_post.* then remove that because it call all data from database and if inner query get less record then tb.post then also it display max record(tb_post)

Upvotes: 1

Related Questions