I think Inner Join problem 1

[shauns1]

I am Selecting all of the rows from a main table (cms_WhatsHot) along with related rows from other tables (cms_Segments and cms_SegmentsParents) all joined using Inner join. The main table (cms_WhatsHot) will always have rows, the joined tables may have no related rows. The result is that if a related row is empty it doesn't return the row from the main table at all.

This is my SQL:

SELECT cms_WhatsHot.whatsHotID, ISNULL(cms_WhatsHot.whatsHotTitle, '') AS whatsHotTitle, ISNULL(cms_WhatsHot.whatsHotDescription, '')
AS whatsHotDescription, ISNULL(cms_WhatsHot.whatsHotSegmentID, '0') AS whatsHotSegmentID, ISNULL(cms_WhatsHot.whatsHotExternalURL, '')
AS whatsHotExternalURL, ISNULL(cms_WhatsHot.whatsHotExternalURLImage, '') AS whatsHotExternalURLImage,
ISNULL(cms_Segments.segmentParentID, '0') AS segmentParentID, ISNULL(cms_Segments.segmentTitle, '') AS segmentTitle,
ISNULL(cms_Segments.segmentImageFile, '') AS segmentImageFile, ISNULL(cms_SegmentParents.parentTemplateURL, '') AS parentTemplateURL
FROM cms_WhatsHot INNER JOIN
cms_Segments ON cms_WhatsHot.whatsHotSegmentID = cms_Segments.segmentID INNER JOIN
cms_SegmentParents ON cms_Segments.segmentParentID = cms_SegmentParents.parentID
ORDER BY cms_WhatsHot.whatsHotOrderID

Should I be using InnerJoin or am I on the wrong track?

Thanks for any help in advance.

s

 

[r937]

use LEFT OUTER JOINs instead of INNER JOINs

r937.com | rudy.ca

 

[shauns1]

Bingo!
Thanks mate.