I have a table of categories and only 1 item requires a subcategory. It seems overkill to create yet another table of subcategories for one item only, especially since the main table will have to search whether it has a category AND a subcategory. Seems there has to be a better way to do it. So that’s question number 1:
what is a better way to do this?
Assuming there is no better way, can I do a conditional WHERE in my SQL statements? Is there a way to do a SQL search that says:
get me all the data in table “tec” with category “cat” and only from table “subcat” if the item does indeed have a subcategory.
I understand that I would need another column in table “tec” that houses the subCat ID. That is what I’m trying to avoid, but if I can’t then hopefully a conditional WHERE can come to the rescue.
PS
The part about getting the info from tec and cat is not a problem, something like:
Well, this looks good for displaying the categories and subcategories, but I think it would lead to problems for displaying the data in the “tec” table when I have to reference other tables as well no?
For example, I need to get the info in table “tec”, but also the info in table “cint” where tec.cintId = cint.id. That’s pretty straightforward, but when I combine the SQL for this with the Left joins from the example in the link, I’m bound to get multiple, unwanted rows (I’m thinking…).
Hallo, I’m revisiting this and the SQL is just not working for me. I have a table such as:
id | cat | translation | pId
5 | Son Mok Sul | Grab wrists | 0
13 | Bakkat Son… | Grab sideways | 5
so for technique #13, the parent is technique #5.
The query of
select root.cat as rootCat, down1.cat as downCat from cat as root left outer join cat as down1 on down1.pId = root.pId order by rootCat, downCat
which follows almost verbatim the query in the link you provided, does not yield correct results. It repeats the first iteration of cat even when the pId’s are not the same.
“almost verbatim” being the important part of your sentence
you were joining on the wrong columns, plus you forgot the WHERE clause
SELECT root.cat AS rootCat
, down1.cat AS downCat
FROM cat AS root
LEFT OUTER
JOIN cat AS down1
ON down1.pId = [COLOR="Red"]root.id [/COLOR]
[COLOR="Blue"]WHERE root.pId = 0[/COLOR]
ORDER
BY rootCat
, downCat
Son Mok Sul Grab Wrists
Son Mok Sul Bakkat Son Grab sideways
and so on and so forth.
The idea is that if the category has a parent (a pId other than 0) then we show first the parent, then the category, then the translation. If it doesn’t have a parent category, we simply show the category name and the translation.
i put the “code” tags in there to confirm the exact spacing that you wanted
those are clearly two separate rules
therefore, you will need two separate subqueries, with UNION to combine them
plus, since you are overlapping the cat of one with the translation of another, we have to “pull some strings” in the SELECT clauses and use generic column names
SELECT cat
, translation AS column2
, NULL AS column3
FROM cat
WHERE pId = 0 -- root
UNION ALL
SELECT root.cat
, down1.cat
, down1.translation
FROM cat AS root
INNER
JOIN cat AS down1
ON down1.pId = root.id
WHERE root.pId = 0
Wow, that’s some pretty complex SQL. I would have never, in a million year, been able to come up with that on my own.
Thank you so very much, this seems to do exactly what I need, except for one tiny thing: I forgot the most important part! the ID
I need a column with the ID of the technique or subtechnique. That is, for those who have parent technique, the ID for that column should be the technique’s ID, not the parent’s.
So for the latest sample you have there, I would ammend it thusly:
5 Son Mok Sul Grab Wrists
13 Son Mok Sul Bakkat Son Grab sideways
Is this possible?
I’ve ammended the SQL like below and it seems to do the trick, but I want to make sure.
SELECT id, cat
, trad AS trad1
, NULL AS trad2
FROM cat
WHERE pId = 0 -- root
UNION ALL
SELECT down1.id, root.cat
, down1.cat
, down1.trad
FROM cat AS root
INNER
JOIN cat AS down1
ON down1.pId = root.id
WHERE root.pId = 0
is there a way to separate the column names so that “trad” will always be the translation column and subcat will be the category that contains a parent?
Right now the third column can contain either a translation or a technique. I’d like to be able to have the column named “trad” always be the translation and the one named “subcat” (which we don’t have here) always contain the subtecnique (technique with parent).
But maybe that’s just not possible or I’m asking too much?
and why? because that’s exactly the way you said you wanted it
okay, try this –
SELECT id
, cat
, NULL AS subcat
, translation AS trad
FROM cat
WHERE pId = 0 -- root
UNION ALL
SELECT down1.id
, root.cat
, down1.cat
, down1.translation
FROM cat AS root
INNER
JOIN cat AS down1
ON down1.pId = root.id
WHERE root.pId = 0
results –
[COLOR="Blue"][B]id cat subcat trad[/B][/COLOR]
5 Son Mok Sul NULL Grab wrists
13 Son Mok Sul Bakkat Son Grab sideways
And, I know I’m pushing my luck here, but if I wanted to obtain all that info and narrow it down by a particular id? The things I tried didn’t work.
I promise this is the last question.
BTW, don’t know if you’re into Martial Arts at all, but this is all for a Hapkido database that I’m building and of course you are very welcome to use it any time you like once it’s live
true, 2 IDs, well, honestly I need to narrow it down by both. One, the id (not the pId) in one occasion and on another query I need the pId. I I had to choose only 1, it would have to be the id.
