Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2002

    Unanswered: Left join not returning rows

    FROM [tblSections]
    LEFT JOIN [tblSectionContents] ON [tblSectionContents].[sectionID] = [tblSections].[id]
    LEFT JOIN [tblCopy] ON [tblSectionContents].[articleID] = [tblCopy].[id]
    LEFT JOIN [tblFiles] ON [tblSectionContents].[fileID] = [tblFiles].[id]
    LEFT JOIN [tblGroupings] ON [tblFiles].[groupingID] = [tblGroupings].[id]
    LEFT JOIN [tblLinks] ON [tblSectionContents].[linkID] = [tblLinks].[id]
    WHERE [tblSections].[id]=2
    If I pass it the ID of a section that has files or copy or [stuff in other tables] attached, then I get a result set that makes sense.

    But if I pass it a section ID that doesn't reference any other content tables (ie: the section just has a title and a link URL), I don't get anything back.

    Shouldn't it should still get me the fields from the row in tblSections that matches the ID I'm passing it?

    How can I make it so that it does?


  2. #2
    Join Date
    Jan 2004
    In a large office with bad lighting
    table ddl, sample data, test results, and expected results would help.

    -- This is all just a Figment of my Imagination --

  3. #3
    Join Date
    Jan 2002
    Umm. This is where it gets a bit tricky. The SQL Server in question is on managed hosting, I don't have Enterprise Manager or Query Analyser or anything like that. I have no front end interface onto this at all, other than stuff I've built myself. What I DO have is an ASP form that I can bang SQL statements into, that'll tell me it went wrong if it goes wrong, and ASP function that'll ouput something approximating a table schema to screen for me. I've copied and pasted the output below, I hope it's of some use.

    [tblSections].[id] adSmallInt(2)
    [tblSections].[navText] adVarChar(20)
    [tblSections].[pageTitle] adVarChar(150)
    [tblSections].[sectionURL] adVarChar(150)
    [tblSections].[sectionOrder] adInteger(4)

    [tblSectionContents].[id] adSmallInt(2)
    [tblSectionContents].[sectionID] adInteger(4)
    [tblSectionContents].[articleID] adInteger(4)
    [tblSectionContents].[fileID] adInteger(4)
    [tblSectionContents].[linkID] adInteger(4)

    So... "sample data". The data that I'm passing it is a section ID: in this case the integer "2". Is that what you mean?

    Test results and expected results? Well, I expect it to work and when I test it, it doesn't.

    Briefly: a section can either have a URL, or else is composed of groups of content (files, copy, etc).
    [tblSections] is linked to the "content" tables ([tblFiles], [tblCopy], etc) via [tblSectionContents]. If the section ID that I'm passing in occurs in [tblSectionContents].[sectionID], (ie: the section is composed of groups of content), then I get a full result set with that content in: one row for each file, or copy item, or whatever, in the section, with the 'global' data from [tblSections] duplicated in each.

    But, if there AREN'T any rows in [tblSectionContents] with that section ID in the [sectionID] field, I don't get any results at all. And I'm expecting one row to be returned, with nulls in the fileID and articleID fields but with the name of the section and the URL it should be linking to.

    I apologise if the way I'm asking the question is making it less straightforward to answer; as you can see I'm slightly constrained by my server setup and my own inexperience.

    Thanks for any advice you can offer.

  4. #4
    Join Date
    Jun 2003
    Provided Answers: 1
    Yes, the left join syntax will return records from [tblSections] regardless of whether there are child records in other tables. So there is something else going on. Is it possible that there are additional criteria in the WHERE clause referencing child tables (which would negate the effect of the LEFT OUTER JOIN)?

    Chances are it is an error in your processing of the data once it is returned from the database.
    If it's not practically useful, then it's practically useless.

    blindman "sqlblindman"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts