Trouble pulling join into single row.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Trouble pulling join into single row.

Author

Topic

j_energy
Starting Member

3 Posts

Posted - 2009-08-12 : 18:20:19

Below is a shortend version of a larger query - enough to get the gist of the problem.

For each product, I am pulling basics like name and code from the PRODUCTS table. Also pulling some info from the INVENTORY table.

Lastly for each record I am trying to pull top level product categories and two levels of product subcatgagories into each row from the CATEGORIES table which is tied to the PRODUCTS through the PRODUCTCATEGORIES table.

In the query below, I get all the results I'm looking for, however each product comes back as three rows, each with a different but single category filling all 3 category columns in that row. Can you nudge me to a good tutorial or approach that would help me solve this?

Thanks in advance!


SELECT
Products.Code,
Products.Name,
Inventory.QtyInStock,
Categories.Name AS Category1,
Categories.Name AS Category2,
Categories.Name AS Category3
FROM
Products LEFT JOIN
Inventory ON Products.uid = Inventory.ProductID INNER JOIN
ProductCategory ON Products.uid = ProductCategory.ProductID INNER JOIN
Categories ON ProductCategory.CategoryID = Categories.uid
WHERE
(Categories.ParentLevel = '0') OR
(Categories.ParentLevel = '1') OR
(Categories.ParentLevel = '2') AND
(Products.Name <> 'No Product Name')
ORDER BY
Products.Code

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-08-12 : 18:26:07

An approach would be to join Categories 3 times with different aliases.

No, you're never too old to Yak'n'Roll if you're too young to die.

j_energy
Starting Member

3 Posts

Posted - 2009-08-13 : 14:40:37

quote:
Originally posted by webfred

An approach would be to join Categories 3 times with different aliases.

Thank you for the nudge webfred. Half way there now but still overlooking something. Each category is showing up in the appropriate column, but each product is still returned in three seperate rows, once for each category level and I am not sure how to make the union work. Another nudge would be appreciated :)

Here is an example of the results:

CODE          NAME          QTYINSTOCK     NAME    NAME    NAME
ProductCode1  ProductName1  5              TOPCAT
ProductCode1  ProductName1  5                      SUBCAT
ProductCode1  ProductName1  5                              SUBSUBCAT
ProductCode2  ProductName2  3              TOPCAT
ProductCode2  ProductName2  3                      SUBCAT
ProductCode2  ProductName2  3                              SUBSUBCAT

Here is the updated query:

SELECT
Products.Code,
Products.Name,
Inventory.QtyInStock,
Category1.Name,
Category2.Name,
Category3.Name
FROM
Products LEFT JOIN
Inventory ON Products.uid = Inventory.ProductID INNER JOIN
ProductCategory ON Products.uid = ProductCategory.ProductID LEFT JOIN
Categories AS Category1 ON ProductCategory.CategoryID = Category1.uid
AND Category1.ParentLevel = '0' LEFT JOIN
Categories AS Category2 ON ProductCategory.CategoryID = Category2.uid
AND Category2.ParentLevel = '1' LEFT JOIN
Categories AS Category3 ON ProductCategory.CategoryID = Category3.uid
AND Category3.ParentLevel = '2'
ORDER BY
Products.Code

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-08-13 : 14:59:43

Try this...

SELECT   Products.Code, 
         Products.Name, 
         Inventory.QtyInStock, 
         MAX(CASE 
               WHEN Category1.Name IS NOT NULL 
               THEN Category1.Name 
               ELSE '' 
             END), 
         MAX(CASE 
               WHEN Category2.Name IS NOT NULL 
               THEN Category2.Name 
               ELSE '' 
             END), 
         MAX(CASE 
               WHEN Category3.Name IS NOT NULL 
               THEN Category3.Name 
               ELSE '' 
             END), 
FROM     Products 
         LEFT JOIN Inventory 
           ON Products.uid = Inventory.ProductID 
         INNER JOIN ProductCategory 
           ON Products.uid = ProductCategory.ProductID 
         LEFT JOIN Categories AS Category1 
           ON ProductCategory.CategoryID = Category1.uid 
              AND Category1.ParentLevel = '0' 
         LEFT JOIN Categories AS Category2 
           ON ProductCategory.CategoryID = Category2.uid 
              AND Category2.ParentLevel = '1' 
         LEFT JOIN Categories AS Category3 
           ON ProductCategory.CategoryID = Category3.uid 
              AND Category3.ParentLevel = '2' 
GROUP BY Products.Code, 
         Products.Name, 
         Inventory.QtyInStock 
ORDER BY Products.Code

j_energy
Starting Member

3 Posts

Posted - 2009-08-13 : 15:11:05

RESOLVED - Thank you vijayisonly.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-08-13 : 15:16:48

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources