mysql query on left join where multiple value

Question

Here is my sql data fiddler http://sqlfiddle.com/#!2/63178/1. Waht's wrong with my query?

SELECT DISTINCT curr.id,curr.curr_tittle, curr.curr_desc 
FROM  wp_curriculum curr LEFT JOIN (SELECT DISTINCT * FROM wp_curriculum_topic WHERE curr_topic IN (4,12)) AS A ON A.curr_id = curr.id ORDER BY A.id

what result set you expect and what is going wrong..pls explain? — plain jane
– plain jane, Commented Sep 18, 2013 at 6:46
can i join these 2 tables : SELECT DISTINCT id,curr_tittle, curr_desc FROM wp_curriculum WHERE id IN (SELECT DISTINCT curr_id FROM wp_curriculum_category WHERE curr_category IN (2,3)) SELECT DISTINCT id,curr_tittle, curr_desc FROM wp_curriculum WHERE id IN (SELECT DISTINCT curr_id FROM wp_curriculum_topic WHERE curr_topic IN (4,12)) — Vasanth
– Vasanth, Commented Sep 18, 2013 at 6:52
We don't know the structure of your table wp_curriculum_category... Also I think curr_id should be a foreign key. — Yann39
– Yann39, Commented Sep 18, 2013 at 6:59

Rajiv Ranjan · Accepted Answer · 2013-09-18 06:52:02Z

1

If you are looking for matching row from both the table then just replace LEFT JOIN to INNER JOIN, otherwise your sql query is showing expected result for LEFT JOIN condition.

SQL Query with INNER JOIN:

SELECT DISTINCT curr.id,curr.curr_tittle, curr.curr_desc FROM  wp_curriculum curr INNER JOIN (SELECT DISTINCT * FROM wp_curriculum_topic WHERE curr_topic IN (4,12)) AS A ON curr.id = A.curr_id ORDER BY A.id

answered Sep 18, 2013 at 6:52

Rajiv Ranjan

1,8691 gold badge12 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

John Woo Over a year ago

you can actually do it without using subquery.

John Woo Over a year ago

SELECT DISTINCT curr.id,curr.curr_tittle, curr.curr_desc  FROM  	wp_curriculum curr  		INNER JOIN wp_curriculum_topicAS A  			ON curr.id = A.curr_id  WHERE a.curr_topic IN (4,12) ORDER BY A.id

sskoko · Accepted Answer · 2013-09-18 06:58:02Z

0

Your query works as expected. Could it be you are mixing ID and CURR_ID?

answered Sep 18, 2013 at 6:58

sskoko

8397 silver badges18 bronze badges

Collectives™ on Stack Overflow

mysql query on left join where multiple value

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related