Re: Query Help

On Tue, 23 Jul 2002, Hunter Hillegas wrote:

> I'm looking for a little query help, if anyone has a free moment...
>
> I have two tables:
>
> CREATE TABLE message_board_topics (
> rec_num integer NOT NULL PRIMARY KEY,
> topic_name varchar(255),
> topic_body text,
> topic_author varchar(20),
> topic_author_email varchar(50),
> topic_date date,
> topic_updated datetime,
> number_of_comments integer
> );
>
> CREATE TABLE message_board_comments (
> rec_num integer NOT NULL PRIMARY KEY,
> topic_id integer REFERENCES message_board_topics,
> comment_parent integer,
> comment_name varchar(255),
> comment_body text,
> comment_author varchar(20),
> comment_author_email varchar(50),
> comment_date date
> );
>
> Anyway, I want to do a quick and dirty search against them... Basically I
> want to find any message_board_topic where the submitted text matches either
> the topic_name, topic_body, topic_author, or the comment_author of any
> related comment...
>
> I tried this:
>
> SELECT * FROM message_board_topics, message_board_comments WHERE
> upper(topic_name) LIKE upper('%test%') OR upper(topic_body) LIKE
> upper('%test%') OR upper(topic_author) LIKE upper('%test%') OR
> (upper(message_board_comments.comment_author) LIKE upper('%test%') AND
> message_board_comments.topic_id = message_board_topics.rec_num) ORDER BY
> message_board_topics.rec_num DESC;
>
> Now, this returns no rows but this query that doesn't search the comments
> table returns a row:
>
> SELECT * FROM message_board_topics WHERE upper(topic_name) LIKE
> upper('%test%') OR upper(topic_body) LIKE upper('%test%') OR
> upper(topic_author) LIKE upper('%test%') ORDER BY
> message_board_topics.rec_num DESC;
>
> Any ideas what I am doing wrong? It would be a big help.

Presumably there's no comment for the matching row. You're doing
an inner join in the first query so it's only going to give you
a row out if there exists a comment whose topic_id is equal to
the other table's rec_num.

You might want to do an outer join instead (something like:
select * from message_board_topics left join message_board_comments
on (message_board_comments.topic_id=message_board_topics.rec_num)
where
upper(topic_name) LIKE upper('%test%') OR upper(topic_body) LIKE
upper('%test%') OR upper(topic_author) LIKE upper('%test%') OR
(upper(message_board_comments.comment_author) LIKE upper('%test%')
order by message_board_topics.rec_num desc;