Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2004
    Posts
    5

    Unanswered: Return one result from a vertical table

    I am struggling with a query which requires returns from 2 tables.

    Table A is called Items and has one record for each item with a unique ID.

    Table B is a verticle table called Attribute_Values and contains N records for each record in table A linked by a column called Object_ID. Also, each Attribute Type has a unique ID

    I am trying to write a query which returns all of the Items from table a based on my criteria and then also returns the AttributeValue record for a specific Attribute Type ID if it exists. If not, still return the Item from Table A with a ‘null’ for the attribute value.

    Any pointers would be greatly appreciated.


    For what it’s worth, This is what gets me my Items.

    SELECT dbo.Owners.Display_Name AS Customer, dbo.Items.Display_Name AS [Drawing Name],dbo.Items.Description AS [Drawing Description], dbo.Folder_Contents.Display_Order, dbo.Folder_Contents.Folder_Id, dbo.Items.Status, dbo.Products.Display_Name AS Project, dbo.Items.ID

    FROM dbo.Items INNER JOIN dbo.Folder_Contents ON dbo.Items.Id = dbo.Folder_Contents.Object_Id INNER JOIN dbo.Owners ON dbo.Items.Owner_Id = dbo.Owners.Id INNER JOIN dbo.Folders ON dbo.Folder_Contents.Folder_Id = dbo.Folders.Id INNER JOIN
    dbo.Folder_Child_Folders ON dbo.Folders.Id = dbo.Folder_Child_Folders.Child_Folder_Id INNER JOIN
    dbo.Folders Folders_1 ON dbo.Folder_Child_Folders.Parent_Folder_Id = Folders_1.Id INNER JOIN dbo.Folder_Child_Folders Folder_Child_Folders_1 ON Folders_1.Id = Folder_Child_Folders_1.Child_Folder_Id INNER JOIN
    dbo.Folders Folders_2 ON Folder_Child_Folders_1.Parent_Folder_Id = Folders_2.Id INNER JOIN dbo.Products ON Folders_2.Id = dbo.Products.Folder_Id

    WHERE (dbo.Items.Status = 2) AND (dbo.Items.Is_Set = 0) AND (dbo.Folder_Contents.Folder_Id = 'E167B175-7BDF-4A01-98AD-92BD59E036F7')

    ORDER BY dbo.Folder_Contents.Display_Order

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    to answer your question:
    Quote Originally Posted by morem
    I am trying to write a query which returns all of the Items from table a based on my criteria and then also returns the AttributeValue record for a specific Attribute Type ID if it exists. If not, still return the Item from Table A with a ‘null’ for the attribute value.
    you have perfectly described a LEFT OUTER JOIN

    in order to understand you query, i had first to rewrite it so that it's a bit easier to read
    Code:
    SELECT dbo.Owners.Display_Name AS Customer
         , dbo.Items.Display_Name AS [Drawing Name]
         , dbo.Items.Description AS [Drawing Description]
         , dbo.Folder_Contents.Display_Order
         , dbo.Folder_Contents.Folder_Id
         , dbo.Items.Status
         , dbo.Products.Display_Name AS Project
         , dbo.Items.ID
      FROM dbo.Items 
    INNER 
      JOIN dbo.Folder_Contents 
        ON dbo.Items.Id = dbo.Folder_Contents.Object_Id 
    INNER 
      JOIN dbo.Owners 
        ON dbo.Items.Owner_Id = dbo.Owners.Id
    INNER 
      JOIN dbo.Folders 
        ON dbo.Folder_Contents.Folder_Id = dbo.Folders.Id 
    INNER 
      JOIN dbo.Folder_Child_Folders 
        ON dbo.Folders.Id = dbo.Folder_Child_Folders.Child_Folder_Id
    INNER 
      JOIN dbo.Folders Folders
        ON dbo.Folder_Child_Folders.Parent_Folder_Id = Folders_1.Id 
    INNER 
      JOIN dbo.Folder_Child_Folders Folder_Child_Folders_1 
        ON Folders_1.Id = Folder_Child_Folders_1.Child_Folder_Id 
    INNER 
      JOIN dbo.Folders Folders_2 
        ON Folder_Child_Folders_1.Parent_Folder_Id = Folders_2.Id 
    INNER 
      JOIN dbo.Products 
        ON Folders_2.Id = dbo.Products.Folder_Id
     WHERE (dbo.Items.Status = 2) 
       AND (dbo.Items.Is_Set = 0) 
       AND (dbo.Folder_Contents.Folder_Id = 'E167B175-7BDF-4A01-98AD-92BD59E036F7')
    please explain which one is table A and which one is table B

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Aug 2004
    Posts
    5

    Smile

    Quote Originally Posted by r937
    to answer your question:you have perfectly described a LEFT OUTER JOIN
    please explain which one is table A and which one is table B

    Thanks for the speedy reply. Actually, I don't have table B included here yet. Table B is called ATTRIBUTE_VALUES. What I need is to take match all item.ids (in current SELECT) to attribute_values.object_id then find all attribute_values.att_value that has an attribute_values.id of 'C834243243' I'm still getting the hang of this Join thing.

  4. #4
    Join Date
    Aug 2004
    Posts
    5
    Looks like I'm close now. I added the following

    LEFT OUTER
    JOIN dbo.Attribute_Values
    ON dbo.Items.Id = dbo.Attribute_Values.Object_Id
    WHERE
    dbo.Items.Is_Set = 0
    AND dbo.Items.Status = 2
    AND dbo.Folder_Contents.Folder_Id = 'C6E34BD2-F294-4471-BB27-D83BD4C46DF'
    AND dbo.Attribute_Values.Attribute_Id = 'C834243243'

  5. #5
    Join Date
    Aug 2004
    Posts
    5
    Looks like I'm still where I've been all day. What happens is it returns only records that have an Attribute_ID as specified in the WHERE Clause. I should return all Items with a dbo.Folder_Contents.Folder_Id = 'C6E34BD2-F294-4471-BB27-AD83BD4C46DF and only the 'Attribute_Values.Attr_Value AS Discipline' if it exists.

    Some ITEMS do not have an Attribute_Values.Attribute_Id equal to the one specified.

    Hope my formatting is better


    Code:
    SELECT TOP 100 PERCENT dbo.Owners.Display_Name AS Customer
    ,dbo.Products.Display_Name AS Project
    ,dbo.Items.Display_Name AS [Drawing Name]
    ,dbo.Items.Description AS [Drawing Description]
    ,dbo.Attribute_Values.Attr_Value AS Discipline
    
    FROM dbo.Items
    INNER
     JOIN dbo.Folder_Contents
      ON dbo.Items.Id = dbo.Folder_Contents.Object_Id
    INNER
     JOIN dbo.Owners 
       ON dbo.Items.Owner_Id = dbo.Owners.Id 
    INNER
     JOINdbo.Folders
      ON dbo.Folder_Contents.Folder_Id = dbo.Folders.Id 
    INNER
     JOIN dbo.Folder_Child_Folders
      ON dbo.Folders.Id = dbo.Folder_Child_Folders.Child_Folder_Id
    INNER
     JOIN dbo.Folders Folders_1
      ON dbo.Folder_Child_Folders.Parent_Folder_Id = Folders_1.Id 
    INNER
     JOIN dbo.Folder_Child_Folders Folder_Child_Folders_1 
       ON Folders_1.Id = Folder_Child_Folders_1.Child_Folder_Id 
    INNER 
     JOIN dbo.Folders Folders_2 
      ON Folder_Child_Folders_1.Parent_Folder_Id = Folders_2.Id 
    INNER
     JOIN dbo.Products 
      ON Folders_2.Id = dbo.Products.Folder_Id 
    LEFT OUTER 
     JOIN dbo.Attribute_Values 
      ON dbo.Items.Id = dbo.Attribute_Values.Object_Id
    
    WHERE dbo.Items.Is_Set = 0 
     AND dbo.Items.Status = 2 
    AND dbo.Folder_Contents.Folder_Id = 'C6E34BD2-F294-4471-BB27-AD83BD4C46DF' 
    AND dbo.Attribute_Values.Attribute_Id = '3FEE30CF-99DC-4C15-84D3-7191C8B13F69' 
    
    ORDER 
     BY dbo.Folder_Contents.Display_Order

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    this is subtle, but you need to change this --
    Code:
    LEFT OUTER 
      JOIN dbo.Attribute_Values 
        ON dbo.Items.Id = dbo.Attribute_Values.Object_Id
     WHERE dbo.Items.Is_Set = 0 
       AND dbo.Items.Status = 2 
       AND dbo.Folder_Contents.Folder_Id = 'C6E34BD2-F294-4471-BB27-AD83BD4C46DF' 
       AND dbo.Attribute_Values.Attribute_Id = '3FEE30CF-99DC-4C15-84D3-7191C8B13F69'
    to this --
    Code:
    LEFT OUTER 
      JOIN dbo.Attribute_Values 
        ON dbo.Items.Id = dbo.Attribute_Values.Object_Id 
       AND dbo.Attribute_Values.Attribute_Id = '3FEE30CF-99DC-4C15-84D3-7191C8B13F69'
     WHERE dbo.Items.Is_Set = 0 
       AND dbo.Items.Status = 2 
       AND dbo.Folder_Contents.Folder_Id = 'C6E34BD2-F294-4471-BB27-AD83BD4C46DF'
    with the condition in the WHERE clause, the left outer join is executed, and the dbo.Attribute_Values row, whether it has that Attribute_Id value or not, is returned by the join, but then the WHERE condition is applied, and for unmatched rows, the Attribute_Id value, which is NULL, which does not equal the specified value, so that row is eliminated, in effect returning the same results as if it were an inner join

    on the other hand, with the condition in the ON clause, it becomes a condition of the join, so if there's isn't a matching dbo.Attribute_Values row with the specified Attribute_ID value, then you have an unmatched condition, in which case you still get the item

    it will make sense after you look at it a few times, trust me

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Aug 2004
    Posts
    5
    Perfect Sense. Thanks for the help!!!

Posting Permissions

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