Query randomly returning all items or just some items

Question

I have 4 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    |                |
+-----------------------+----------------+------+-----+---------+----------------+


mysql> describe users;
+--------------+---------------+------+-----+---------+----------------+
| Field        | Type          | Null | Key | Default | Extra          |
+--------------+---------------+------+-----+---------+----------------+
| user_id      | int(10)       | NO   | PRI | NULL    | auto_increment |
| first_name   | varchar(100)  | NO   |     | NULL    |                |
| last_name    | varchar(100)  | NO   |     | NULL    |                |
| email        | varchar(150)  | NO   |     | NULL    |                |
| user_pass    | varchar(40)   | NO   |     | NULL    |                |
| zip          | varchar(100)  | NO   |     | NULL    |                |
| country      | varchar(100)  | NO   |     | NULL    |                |
| city         | varchar(100)  | NO   |     | NULL    |                |
| state        | varchar(100)  | NO   |     | NULL    |                |
| lat          | float(9,6)    | YES  |     | NULL    |                |
| lng          | float(9,6)    | YES  |     | NULL    |                |
| agreed_terms | tinyint(1)    | YES  |     | NULL    |                |
| join_date    | date          | NO   |     | NULL    |                |
| last_login   | date          | NO   |     | NULL    |                |
| bio_blurb    | varchar(5000) | YES  |     | NULL    |                |
+--------------+---------------+------+-----+---------+----------------+
15 rows in set (0.03 sec)

mysql> describe member_photo;
+-------------------+---------------+------+-----+---------+----------------+
| Field             | Type          | Null | Key | Default | Extra          |
+-------------------+---------------+------+-----+---------+----------------+
| photo_id          | int(10)       | NO   | PRI | NULL    | auto_increment |
| member_id         | int(10)       | NO   |     | NULL    |                |
| photo_description | varchar(3000) | YES  |     | NULL    |                |
| photo_path        | varchar(1000) | NO   |     | NULL    |                |
| small_thumb       | varchar(1000) | YES  |     | NULL    |                |
| mid_thumb         | varchar(1000) | YES  |     | NULL    |                |
| is_main_photo     | tinyint(1)    | YES  |     | NULL    |                |
+-------------------+---------------+------+-----+---------+----------------+

And I have a query like this:

select comment_id,
       commenter_id,
       section_name,
       comment,
       solution_part,
       display_order,
       solution_section_id,
       suggested_solution_id, 
       DAYOFMONTH(date),
       DAYNAME(date),
       YEAR(date),
       MONTH(date),
       first_name,
       last_name,
       email,
       small_thumb,
       mid_thumb 
from solution_sections 
left join suggested_solution_comments on
    solution_sections.solution_section_id = suggested_solution_comments.solution_part 
left join users on
    suggested_solution_comments.commenter_id = users.user_id  
left join member_photo on
    suggested_solution_comments.commenter_id = member_photo.member_id
where suggested_solution_id = 61 OR 
      suggested_solution_id IS NULL
order by solution_section_id,
         comment_id,
         section_name,
         comment,
         solution_part,
         display_order;

What its supposed to do is get each section_name from the solution_sections table, and then find the comments (and data about who commented). Sometimes there are no comments, but it should still return at least the row with section_name and all other things being null.

But for some reason it does not. And the weirdest part is that if I give it a different suggested_solution_id to match, it will return all of the rows of solution_sections.

Any ideas why such a thing might happen? Thank you!!

And I just realized one thing - if another comment has been made for any problem_id, this query won't return the row with that section.

I am testing it now, and it seems the clause is where the bug is, but I am not sure what is wrong with it exactly. — GeekedOut, Nov 08 '11 at 14:16

Michał Powaga · Answer 1 · 2011-11-08T14:42:00.913

1

If you want to show solution_sections even if all the rest doesn't exist, you can use "left outer join":

select comment_id,commenter_id, section_name, comment, solution_part, 
    display_order, solution_section_id, suggested_solution_id, 
    DAYOFMONTH(date), DAYNAME(date), YEAR(date), MONTH(date), 
    first_name, last_name, email, small_thumb,mid_thumb 
from solution_sections 
left outer join suggested_solution_comments on  solution_sections.solution_section_id = suggested_solution_comments.solution_part 
    and suggested_solution_id = 61
left outer join users on  suggested_solution_comments.commenter_id = users.user_id  
left outer join member_photo on  suggested_solution_comments.commenter_id = member_photo.member_id
where solution_section_id = ????
order by solution_section_id, comment_id, section_name, comment, solution_part,display_order;

ps. try to use aliases for tables it's more readable :-)

edited Nov 08 '11 at 14:42

answered Nov 08 '11 at 14:21

Michał Powaga

22,561
8
51
62

thank you - could you remind me what it the implication of the outer join? Have not used those in a long time lol – GeekedOut Nov 08 '11 at 14:24
just tried the outer-join solution and it still didn't work unfortunately – GeekedOut Nov 08 '11 at 14:28
1

[`left join` and `left outer join` are the same thing](http://stackoverflow.com/questions/406294/left-join-and-left-outer-join-in-sql-server) – Chris Shouts Nov 08 '11 at 14:29
You are right, sorry, my fault. I wonder whether adding "and suggested_solution_id = 61" to "left outer join suggested_solution_comments on" will correct it. – Michał Powaga Nov 08 '11 at 14:43
@Michal That actually did it!!! I have to test further, but that seemed to have done it. I am not entirely sure I get it. What just happened here? :) – GeekedOut Nov 08 '11 at 14:52
When restriction to specific suggested_solution_id (here 61) is in "where" clause it will limit final result set to those solution_sections which are connected to such a suggested_solution_comments. Otherwise when it's placed in "on" clause it will not give such a effect, then query will show all rows from solution_sections and related from suggested_solution_comments if the condition is fulfilled otherwise null values. – Michał Powaga Nov 08 '11 at 15:02

StevieG · Accepted Answer · 2011-11-08T15:02:05.740

You need a left outer join to view all records from your parent table when child records are not guaranteed to exits. I'd also avoid adding a where clause when using outer joins.. I think its more readable to keep your join in a subselect, and filter the results.. Try something like this:

select * from 
(
    select sc.comment_id,
           sc.commenter_id,
           ss.section_name,
           sc.comment,
           sc.solution_part,
           ss.display_order,
           ss.solution_section_id,
           sc.suggested_solution_id, 
           DAYOFMONTH(sc.date),
           DAYNAME(sc.date),
           YEAR(sc.date),
           MONTH(sc.date),
           u.first_name,
           u.last_name,
           u.email,
           mp.small_thumb,
           mp.mid_thumb 
    from solution_sections ss
    left outer join suggested_solution_comments sc on ss.solution_section_id = sc.solution_part
    left outer join users u on sc.commenter_id = u.user_id  
    left outer join member_photo mp on sc.commenter_id = mp.member_id) a
where a.suggested_solution_id = 61 OR 
      a.suggested_solution_id IS NULL
order by a.solution_section_id,
         a.comment_id,
         a.section_name,
         a.comment,
         a.solution_part,
         a.display_order;

EDIT:

select sc.comment_id,
       sc.commenter_id,
       ss.section_name,
       sc.comment,
       sc.solution_part,
       ss.display_order,
       ss.solution_section_id,
       sc.suggested_solution_id, 
       DAYOFMONTH(sc.date),
       DAYNAME(sc.date),
       YEAR(sc.date),
       MONTH(sc.date),
       u.first_name,
       u.last_name,
       u.email,
       mp.small_thumb,
       mp.mid_thumb 
from solution_sections ss
left outer join suggested_solution_comments sc on ss.solution_section_id = sc.solution_part 
                                               AND sc.suggested_solution_id = 61
left outer join users u on sc.commenter_id = u.user_id  
left outer join member_photo mp on sc.commenter_id = mp.member_id
order by solution_section_id,
         comment_id,
         section_name,
         comment,
         solution_part,
         display_order;

that gave an error message "every delivered table must have its own alias" — GeekedOut, Nov 08 '11 at 14:30
This seems exactly the same as my original except with the table aliases, no? — GeekedOut, Nov 08 '11 at 14:39
no error message this time, but the query still has the issue mine had where it returns not all rows from the solution_sections table — GeekedOut, Nov 08 '11 at 14:42
it returns rows of the solution_sections table where there are no matching comments - so its pretty strange — GeekedOut, Nov 08 '11 at 14:50
what does the suggested_solution_id = 61 do? It fixed the part where all the solution_sections are returned, but now the associated comments are not returned alongside it :) — GeekedOut, Nov 08 '11 at 15:00

Query randomly returning all items or just some items

2 Answers2