asked on

Parent-Child SQL

I have two table as below:

1. Table1 : TblCategory
Field: catID, CatNameEng, CatNameKh, MainID, DateUpdate, UserId
This table contain Parent-Child. Field "MianID" is parent.

below is result of parent-child query:

select tblcategory.catnameeng CateNameEng, down1.catnameeng Sub1, down2.catnameeng sub2
from TblCategory
 left outer join TblCategory as down1 on TblCategory.CatID=down1.MainID
 left outer join TblCategory as down2 on down1.catid=down2.mainid
 where TblCategory.MainID='0' and TblCategory.CatID >'0'

Open in new window

2. Table2 : TblTVCSpot

How to get result as in excel

Thanks,
Chou
result.xlsx

Vitor Montalvão

WITH CTE_Cat AS 
(
    SELECT TblCategory.CatID,  tblcategory.catnameeng CateNameEng, down1.catnameeng Sub1, down2.catnameeng sub2
    FROM TblCategory
        LEFT OUTER JOIN TblCategory as down1 on TblCategory.CatID=down1.MainID
        LEFT OUTER JOIN TblCategory as down2 on down1.catid=down2.mainid
    WHERE TblCategory.MainID='0' and TblCategory.CatID >'0' 
)
SELECT CTE_Cat.catnameeng,  CTE_Cat.Sub1, CTE_Cat.Sub2,  TblTVCSpot.TVCName
FROM TblTVCSpot
    INNER JOIN CTE_Cat ON TblTVCSpot.CatID = CTE_Cat.CatID

Open in new window

tektnoutchou

ASKER

I had perform it but it not return any data.

Vitor Montalvão

Can you export TblCategory and TblTVCSpot to an Excel file and post it here?

tektnoutchou

ASKER

I change it like this but it can get "TVCName" from "down2.CatID".

WITH CTE_Cat AS 
(
    SELECT TblCategory.CatID,  tblcategory.catnameeng CateNameEng, down1.catnameeng Sub1, down2.catnameeng sub2, down2.CatID downCat2
    FROM TblCategory
        LEFT OUTER JOIN TblCategory as down1 on TblCategory.CatID=down1.MainID
        LEFT OUTER JOIN TblCategory as down2 on down1.catid=down2.mainid
    WHERE TblCategory.MainID='0' and TblCategory.CatID >'0' 
)
SELECT CTE_Cat.catenameeng,  CTE_Cat.Sub1, CTE_Cat.Sub2,  TblTVCSpot.TVCName
FROM TblTVCSpot
    INNER JOIN CTE_Cat ON TblTVCSpot.CatID = CTE_Cat.downCat2

Open in new window

Table.xlsx

Vitor Montalvão

The query isn't returning rows because you want the Main Category Name to be returned.
If you take it out from the SELECT then it will return rows:

WITH CTE_Cat AS 
(
    SELECT tblcategory.catnameeng CateNameEng, down1.catid Cat1, down1.catnameeng Sub1, down2.catid Cat2, down2.catnameeng sub2
    FROM TblCategory
        LEFT OUTER JOIN TblCategory as down1 on TblCategory.CatID=down1.MainID
        LEFT OUTER JOIN TblCategory as down2 on down1.catid=down2.mainid
    WHERE TblCategory.MainID='0' and TblCategory.CatID >'0' 
)
SELECT down1.Sub1, down2.Sub2,  TblTVCSpot.TVCName
FROM TblTVCSpot
    LEFT JOIN CTE_Cat down1 ON TblTVCSpot.CatID = down1.Cat1
    LEFT JOIN CTE_Cat down2 ON TblTVCSpot.CatID = down2.Cat2

Open in new window

ASKER CERTIFIED SOLUTION

Vitor Montalvão

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

Vitor Montalvão

By the way, I was playing around and came with this version:

SELECT Category.Sub1, Category.Sub2, TblTVCSpot.TVCName
FROM TblTVCSpot
INNER JOIN (SELECT C.CatID, C.CatNameEng Sub1, M.CatNameEng Sub2
	FROM TblCategory M
	INNER JOIN TblCategory C ON M.CatID=C.MainID
	WHERE M.MainID>0) Category ON TblTVCSpot.CatID = Category.CatID

Open in new window

Which one fits better on your needs?

tektnoutchou

ASKER

yes, it work with your query but need a little change

	WITH CTE_Cat AS 
(
    SELECT tblcategory.catnameeng CateNameEng, down1.catid Cat1, down1.catnameeng Sub1, down2.catid Cat2, down2.catnameeng sub2
    FROM TblCategory
        LEFT OUTER JOIN TblCategory as down1 on TblCategory.CatID=down1.MainID
        LEFT OUTER JOIN TblCategory as down2 on down1.catid=down2.mainid
    WHERE TblCategory.MainID='0' and TblCategory.CatID >'0' 
)
SELECT CateNameEng, CTE_Cat.sub1, CTE_Cat.sub2, TVCName
FROM CTE_Cat
left join TblTVCSpot on TblTVCSpot.CatID=CTE_Cat.cat2 or TblTVCSpot.CatID=CTE_Cat.Cat1
left join TblBrand on TblTVCSpot.BrandID=TblBrand.BrandID
order by catenameeng, sub1, sub2,BrandName

Open in new window