CODEWITHSUNDEEP
mysqlsqldatabase
GeekedOut
GeekedOut

Reputation: 17185

How to join tables so every item in one of them shows up independent of the joined table

I have 2 tables:

mysql> describe solution_sections;
+---------------------+---------------+------+-----+---------+----------------+
| Field               | Type          | Null | Key | Default | Extra          |
+---------------------+---------------+------+-----+---------+----------------+
| solution_section_id | int(10)       | NO   | PRI | NULL    | auto_increment |
| display_order       | int(10)       | NO   |     | NULL    |                |
| section_name        | varchar(1000) | YES  |     | NULL    |                |
+---------------------+---------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> describe suggested_solution_comments;

+-----------------------+----------------+------+-----+---------+----------------+
| Field                 | Type           | Null | Key | Default | Extra          |
+-----------------------+----------------+------+-----+---------+----------------+
| comment_id            | int(10)        | NO   | PRI | NULL    | auto_increment |
| problem_id            | int(10)        | NO   |     | NULL    |                |
| suggested_solution_id | int(10)        | NO   |     | NULL    |                |
| commenter_id          | int(10)        | NO   |     | NULL    |                |
| comment               | varchar(10000) | YES  |     | NULL    |                |
| solution_part         | int(3)         | NO   |     | NULL    |                |
| date                  | date           | NO   |     | NULL    |                |
+-----------------------+----------------+------+-----+---------+----------------+

What I am trying to do is to display the list of section_name from the solution_sections table. It only has about 10 rows in it. And for every section name, to get the list of suggested_solution_comments associated with it.

The tables are linked by suggested_solution_comments.solution_part and solution_sections.solution_section_id

Here is what I am trying so far:

select section_name , comment , solution_part , display_order from solution_sections 
    left join suggested_solution_comments on 
    solution_sections.solution_section_id = suggested_solution_comments.solution_part   
    where suggested_solution_id = 188
    group by display_order;

But that returns nothing when there are no comments. But even if there are no comments, I'd like to still display the list of section_names from the solution_sections table.

Thanks!!

Upvotes: 0

Views: 60

Answers (3)

Doliveras
Doliveras

Reputation: 1769

By using suggested_solution_id in the where clausule you are eliminating from the result any row that have no content no matching row in suggested_solution_comments table.

If you want to get results even when suggested_solution_comments has no content you can't use this field in the where clausule. or you have to consider the possibility that suggested_solution_id could be NULL.

EDITED to take in consideration the comment by @X-Zero

Upvotes: 1

ean5533
ean5533

Reputation: 8994

The problem is here:

where suggested_solution_id = 188

Your query requires the suggested_solution_id have a value of 188, which will never be true for records that have no comments. Try adding in this:

OR suggested_solution_id IS NULL

Upvotes: 2

BuZz
BuZz

Reputation: 17495

I think your table structure is not the best to do this. if you have just a primary key in each table, and want to perform on join on those, it would need to refer to the same thing.. Otherwise, introducing a foreign key able to join on a primary key in the other table.

Upvotes: 0

Related Questions