INNER JOIN with Clauses - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

INNER JOIN with Clauses

Author

Topic

d473566
Starting Member

23 Posts

Posted - 2002-04-12 : 15:04:34

Hi,
I know this is probably easy but I have a brain cramp, I guess

SELECT
forum.id AS forumID,
forum.policy_section,
forum.name,
mt.id,
mt.topic,
mt.start_date

FROM tbl_messageforums forum

LEFT OUTER JOIN tbl_message_topics mt ON mt.forum_id = forum.id
WHERE forum.policy_section = 0
AND mt.approved=1

Basically, I want the forum name, and only the topics that have been approved. Problem is, if there are no topics, it doesn't find the forum name either (no records affected). If I take out the "AND mt.approved=1" line, works OK but it is not what I want or need.

Please help me!

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-04-12 : 15:11:17

This should fix it:

SELECT
forum.id AS forumID,
forum.policy_section,
forum.name,
mt.id,
mt.topic,
mt.start_date

FROM tbl_messageforums forum

LEFT OUTER JOIN tbl_message_topics mt ON mt.forum_id = forum.id
WHERE forum.policy_section = 0
AND IsNull(mt.approved, 1)=1

In a LEFT JOIN, rows in the right-hand table that DO NOT match the left-hand table have NULL in there columns. Therefore, by setting a condition in the WHERE clause that tests the right-hand table, you effectively turn the LEFT JOIN into an INNER JOIN.

In this case, the IsNull fuction checks the right table, and substitutes a 1 if it finds a Null value. That makes your WHERE clause true, and return the results you want.

d473566
Starting Member

23 Posts

Posted - 2002-04-12 : 15:28:52

Thanks for the code, but I don't think it is quite what I need.

The approved column has a default of 0. When I added your code, it only worked if I replaced 0 with a NULL in the table. When I ran it, The iSNull will only return those fields where approved is NULL. If all rows in the mt table are 0, there is no records returned.

Any thoughts?

d473566
Starting Member

23 Posts

Posted - 2002-04-12 : 15:31:19

Am I explaining this OK? Should I post more code?

Edited by - d473566 on 04/12/2002 15:48:54

drsloat
Starting Member

45 Posts

Posted - 2002-04-12 : 17:15:52

Try This:

SELECT
forum.id AS forumID,
forum.policy_section,
forum.name,
mt.id,
mt.topic,
mt.start_date

FROM tbl_messageforums forum

LEFT OUTER JOIN tbl_message_topics mt ON (mt.forum_id = forum.id AND mt.approved=1 )
WHERE forum.policy_section = 0

d473566
Starting Member

23 Posts

Posted - 2002-04-12 : 17:57:56

That is the code that makes it happen!!
Thanks for the help- much appreciated.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources