CODEWITHSUNDEEP
mysqlsqldatabasejoin
ShadowStorm
ShadowStorm

Reputation: 851

Which join type is required for my query

I have a reputation points table for my members.

rep_id   |   mem_id   |   activity        |   points   |   article_id   |   comment_id
---------|------------|-------------------|------------|----------------|-----------------
1        |   99       |   Posted Comment  |   5        |   10343        |   239403
2        |   99       |   Introduction    |   50       |                |
3        |   99       |   Liked Comment   |   5        |   748          |   9302

As you can see, not all reputation points have an article_id or a comment_id, but when I list these records, if the record does have an article_id, I need to grab the article_title from the blog_articles table. The same with comment_id, if it's present, I need to grab the comment from the blog_comments table. If article_id or comment_id is missing, just print the original record as-is.

I would really appreciate if somebody could tell me which join to use for this query. I THINK, by looking at the documentation, it's a LEFT JOIN I need, which prints all records from the left, even though there are no matches in the right - but really unsure.

I'm not looking for any code, just tell me which join is recommended and I will code it.

Upvotes: 3

Views: 79

Answers (2)

Joel Coehoorn
Joel Coehoorn

Reputation: 416149

You need an OUTER join. Outer joins come in the LEFT, RIGHT, and FULL varieties. You could write a query using either a LEFT or RIGHT outer join that would work, but probably a LEFT OUTER JOIN (commonly abbreviated just LEFT JOIN) is what you want.

Upvotes: 3

Cezar
Cezar

Reputation: 56372

Left join is what you need, indeed. If the record does not have an article_id the value for article_title will be NULL. The same applies to comment_id / comment.

Upvotes: 3

Related Questions