CODEWITHSUNDEEP
mysqlsqloptimization
Nicolas Racine
Nicolas Racine

Reputation: 1061

make a select in a select statement mysql

Maybe my title is very vague. since I dont know how what im looking for is called.

So here is my problem.

since my navigator on my website is create with two table. like the following.

MainCategory
 -id
 -name
Category
 -id
 -name
 -maincategory
 -sub

Of course the complexity of those two table is more than that but this is the esential for my problem.

So. im trying to create a new nav that could load faster ( hopefully I can get the content of the nav with only one one mysql call )

every category has an maincategory field that is the id that point to the corect MainCategory, Sometime a category is a subCategory of an category.

So im trying to have a row for every category every if it a sub or a simple category

this is my mysql query.

SELECT category.id AS id, category.name AS catName, maincategory.name AS mainCatName,     maincategory.id AS mainCatId, (
category.sub = category.id
) AS catSubName
FROM category
INNER JOIN maincategory ON ( category.maincategory = maincategory.id )

But in the field catSubName i need the name of the category.id

With the query that i tryed to put togther I have this output enter image description here

What I would like tho is the same output but that in the field catSubName to have the categoryname instead. Right now I only have 0 for all category.

Let say that the catName "Cleaning Supplies"(742) is a subCategory of "Others"(743) then in the field "catSubName" of "id" 742 i would like to have the string "Other" of the catName 743

the conent of catSubName should be category.name of the category.sub that is equal of the category.id that the category.name is in.. :S

I hope this is clearing up the confusing question a little. :S

Thanks for any input!

Upvotes: 0

Views: 52

Answers (1)

FrankPl
FrankPl

Reputation: 13315

As your comment suggests category is a recursive hierarchy, where sub refers to the id of the sub category within the same table, you should use:

SELECT c.id AS id,
       c.name AS catName,
       m.name AS mainCatName,
       m.id AS mainCatId,
       s.name AS catSubName
FROM category c
     INNER JOIN maincategory m ON ( c.maincategory = m.id ) 
     LEFT OUTER JOIN category s ON (c.sub = s.id)

I used an outer join here - which delivers results even if the category has no sub category. But I am no sure how your database design is, hence this may need adaptation.

Upvotes: 1

Related Questions