query inner join - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

query inner join

Author

Topic

craigmacca
Posting Yak Master

142 Posts

Posted - 2008-02-20 : 15:53:35

Hi i have a query which i cant get to work,

i need to display categories and sub categories

Category 1
SubCat1
SubCat2

Category 2
SubCat1
SubCat2

but i am actually geting

Category 1
SubCat1
Category 1
SubCat2

Category 2
SubCat1
Category 2
SubCat2

any ideas what i need to do to this query?

select CT.Cat_ID, CT.Cat_Name, ST.SubCat_Name, ST.SubCat_ID
from dbo.Category_Table CT

LEFT JOIN dbo.SubCategory_Table ST ON ST.SubCat_Cat_ID = CT.Cat_ID

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-20 : 16:06:43

[code]SELECT COALESCE(d.SubCat_ID, d.Cat_ID) AS CatID,
COALESCE(d.SubCat_Name, d.Cat_Name) AS CatName
FROM (
SELECT ct.Cat_ID,
ct.Cat_Name,
st.SubCat_Name,
st.SubCat_ID
FROM dbo.Category_Table AS ct
LEFT JOIN dbo.SubCategory_Table AS st ON st.SubCat_Cat_ID = ct.Cat_ID

UNION

SELECT Cat_ID,
Cat_Name,
NULL,
NULL
FROM dbo.Category_Table
) AS d
ORDER BY d.Cat_Name,
d.Cat_ID,
d.SubCat_Name[/code]

E 12°55'05.25"
N 56°04'39.16"

craigmacca
Posting Yak Master

142 Posts

Posted - 2008-02-20 : 16:28:22

thanks tried adding that to my sp but i get

Msg 156, Level 15, State 1, Procedure GetCategories, Line 12
Incorrect syntax near the keyword 'AS'.
Msg 156, Level 15, State 1, Procedure GetCategories, Line 26
Incorrect syntax near the keyword 'NULL'.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-20 : 16:39:09

See edited response. I was missing a right paranthesis and a comma.

E 12°55'05.25"
N 56°04'39.16"

craigmacca
Posting Yak Master

142 Posts

Posted - 2008-02-24 : 14:26:55

ok i can see what you have done which is good but..

i need the Category to be named different to the SubCategory.

i need to be able to display in my webpage as below so when a user clicks on the category the sub categories drop down.

Category
SubCategory
SubCategory
SubCategory

Category
SubCategory
SubCategory
SubCategory

Category
SubCategory
SubCategory
SubCategory

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources