0

Table Name : Feature

+--------+----------+ | fea_id | fea_name | +--------+----------+ | 1 | Price | | 2 | Height | | 3 | Weight | +--------+----------+

Table Name : property_meta 

+----+--------+--------+-------+ | id | fea_id | pro_id | value | +----+--------+--------+-------+ | 100 | 1 | 300 | 2500 | | 200 | 2 | 300 | 300 | | +----+--------+--------+-------+

My Query

SELECT * FROM feature LEFT JOIN property_meta ON feature.fea_id = property_meta.fea_id where property_meta.pro_id=300 GROUP by feature.fea_id ORDER BY feature.fea_id ASC

Expected Result

+--------+--------+-------+ | fea_id | pro_id | value | +--------+--------+-------+ | 1 | 300 | 2500 | | 2 | 300 | 300 | | 3 | 300 | NULL | +--------+--------+-------+

But I am getting without the last row. I need that last row also. How I modify my query to get the last row also?

That means I need to get all rows of Feature table even there is no value in property meta table.

Improve this question
1
  • 2
    Why would pro_id be 300 for fea_id = 3? Commented May 26, 2017 at 8:19

4 Answers 4

Reset to default
3

where property_meta.pro_id=300 makes your left join to an inner join. Add this to the on clause and it is working:

SELECT * FROM feature LEFT JOIN property_meta ON feature.fea_id = property_meta.fea_id and property_meta.pro_id=300 GROUP by feature.fea_id ORDER BY feature.fea_id ASC
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

2 
SELECT * FROM feature LEFT JOIN property_meta ON feature.fea_id = property_meta.fea_id and property_meta.pro_id=300 GROUP by feature.fea_id ORDER BY feature.fea_id ASC

Put the where condition into the join condition as the where condition restricts the result and the join condition only the joined tables

Improve this answer

1 Comment

Do not enter code only. Explain what you have done and why
2 
SELECT * FROM feature LEFT JOIN property_meta ON feature.fea_id = property_meta.fea_id AND property_meta.pro_id=300 -- <-- need to move this condition here or the where clause will remove the last row GROUP by feature.fea_id ORDER BY feature.fea_id ASC
Improve this answer

Comments

0

where property_meta.pro_id=300 removes that line, cause this will be null after the unsuccessfull join.

remove that condition and you will have 

+--------+--------+-------+ | fea_id | pro_id | value | +--------+--------+-------+ | 1 | 300 | 2500 | | 2 | 300 | 300 | | 3 | NULL| NULL | +--------+--------+-------+

Fix accordingly :)

Improve this answer

4 Comments

I can't remove pro_id=300 because there are lots of pro_id's in property_meta. I need to get only for product id 300. Ex: Price of product id 300.
@DamithRuwan Its just an example why the line is removed. Add the condition to the join rather than where as outlined by Jens.
Yes, Thanks. I solved my question using Jens Answer. Thanks for you too. I am not the person who down vote your answer.
No problem :-) idk about downvotes of people expecting a copy&paste solution. (From your current query i expected you to be able to fix it, once you "see" why the row vanishes)

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.