fetching table data with single query

Question

I want to access all data under name='vehicle' so all child's and sub child's should get in single query

enter image description here

I have tried this query

SELECT a.CID as MainCategoryID, b.CID as ChildCategoryID, b.parentID
FROM categories a, categories b
WHERE a.CID = b.parentID AND NAME='vehicle'

How deep your categories can be (how many subchilds ?) ? Have a look at this article : http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/ — Alexandre Nucera, Feb 26 '14 at 11:10
try have a look here: http://stackoverflow.com/questions/16513418/how-to-do-the-recursive-select-query-in-mysql — marcosh, Feb 26 '14 at 11:10

M Reza Saberi · Answer 1 · 2014-02-26T11:24:41.590

0

SELECT a.CID as MainCategoryID, b.CID as ChildCategoryID, b.parentID
FROM categories a
LEFT JOIN categories b ON a.CID = b.parentID
WHERE a.NAME = 'vehicle'
ORDER By a.CID

edited Feb 26 '14 at 11:24

answered Feb 26 '14 at 11:19

M Reza Saberi

7,134
9
47
76

i want query like select .......where name='vehicle' then it should display all record and sub records upto n level under vehicles – Swapnil Feb 26 '14 at 11:21
you need to use join for every single sub-category level – M Reza Saberi Feb 26 '14 at 11:26
but if levels are dynamic then how should i handle query? – Swapnil Feb 26 '14 at 11:29
It does not work like that. You cant join your mainCategory table with dynamic tables. you need to clarify the joining fields and stuff! – M Reza Saberi Feb 26 '14 at 11:55

score 0 · Accepted Answer · answered Feb 26 '14 at 11:40

0

A possible solution should be the following:

SELECT @pv:=cid AS 'cid', name, parentID FROM categories
JOIN (SELECT @pv:=0) tmp
WHERE parentID = @pv

you can find a SQLFiddle here: http://www.sqlfiddle.com/#!2/6c75a0/6

answered Feb 26 '14 at 11:40

marcosh

8,780
5
44
74

actually, I don't know much about variables in MYSQL, I just took inspiration from here (http://stackoverflow.com/questions/16513418/how-to-do-the-recursive-select-query-in-mysql) and did the necessary adaptations. It was a bit of an experiment of learn by doing – marcosh Feb 26 '14 at 11:49
thanks for the query.but i am facing another problem when sub categories are not in sequence. CREATE TABLE categories ( cid INT, name VARCHAR(30), parentID INT ); INSERT INTO categories (cid, name, parentID) VALUES (1, 'vehicles', 0), (2, 'two wheelers', 0), (3, 'two wheelers with gear',1), (4, 'moped',1); here is changed schema for your SQL fiddle with same query i get output as only first subcategory and rest are ignored. Can you help with that? – Swapnil Mar 05 '14 at 06:20
I think that the approach I proposed doesn't work in this case. In the link in my previous post you can find an alternative solution – marcosh Mar 05 '14 at 10:18

fetching table data with single query

2 Answers2

Linked