Return one result from a vertical table

morem · #1 (**permalink**) 08-28-04, 14:01

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

r937 · #2 (**permalink**) 08-28-04, 16:18

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

morem · #3 (**permalink**) 08-28-04, 16:29

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.

morem · #4 (**permalink**) 08-28-04, 16:46

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'

morem · #5 (**permalink**) 08-28-04, 18:01

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

r937 · #6 (**permalink**) 08-28-04, 18:17

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

morem · #7 (**permalink**) 08-28-04, 20:40

Perfect Sense. Thanks for the help!!!

Internet Services

Web Development

Digital Marketing

Consumer Tech