Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2011
    Posts
    63

    Unanswered: Wha can cause this?

    Hi guys, what can cause a working select statement to return all fields blank when inner joining it with a new table?

    Code:
    USE [dbTalesOfEpic]
    GO
    /****** Object:  StoredProcedure [dbo].[getNode]    Script Date: 08/04/2011 20:29:37 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    
    ALTER PROCEDURE [dbo].[getNode]
    
    @mapBoxId int
    
    AS
    BEGIN
    
    	SET NOCOUNT ON;
    
    
    SELECT     mapGrids.tooltip, mapGrids.mapBoxId, mapGrids.alternateText, mapGrids.imageUrl, mapGrids.nodeName, linkTypes.linkType, linkTo.mapLink, 
                          linkTo.locationLink, Links.linkTypeId
    FROM         mapGrids INNER JOIN
                          Links ON mapGrids.linkId = Links.linkId INNER JOIN
                          linkTo ON Links.linksToId = linkTo.linkToId INNER JOIN
                          linkTypes ON Links.linkTypeId = linkTypes.linkTypeId
    WHERE     (mapGrids.mapBoxId = @mapBoxId)
    
    END
    Basically as soon as i add another inner join to another table that also has a relationship, the select returns nothing.

    What can cause this?

    Here is a diagram of what i am trying to join:

    http://img263.imageshack.us/img263/1...fdgfditled.jpg

    As soon as i join LinkTo with either Locations or Maps, the select returns nothing. Why is this?

    The only thing i can attribute this to is that mapLink and locationLink (the fields i am linking the previously mentioned tables on) can be null. Is this what causes this problem?

    Thanks!
    Last edited by TheGateKeeper; 08-04-11 at 15:38.

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    I suspect it is not returning blank fields, but no rows. When you go to the messages tab in Management Studio, I bet you will see there are "0 rows affected", because there are no matching rows in the newly joined table.

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Expanding on MCrowley's idea, try using LEFT JOIN in place of INNER JOIN and see if that helps.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #4
    Join Date
    Jun 2011
    Posts
    63
    Quote Originally Posted by MCrowley View Post
    I suspect it is not returning blank fields, but no rows. When you go to the messages tab in Management Studio, I bet you will see there are "0 rows affected", because there are no matching rows in the newly joined table.
    Yes, it returns no rows. Maybe i dont understand how INNER JOIN works, but if it doesnt match one table why dont the others return something?

    Quote Originally Posted by Pat Phelan View Post
    Expanding on MCrowley's idea, try using LEFT JOIN in place of INNER JOIN and see if that helps.

    -PatP
    Will try.

  5. #5
    Join Date
    Jun 2011
    Posts
    63
    Ok, i looked into full, right, left, and inner joins and i made the SP work.

    However, what is the difference between outer and inner? (asaik, inner returns when both tables have a match).

Posting Permissions

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