Exclusionary MySQL Statement Using Joins

I've been racking my brain for hours on creating a query to drill down into categories of a blog based on the topics they're assigned to in a blog post. For example, let's say I have the categories of Continents, North America, South America, Africa, Antarctica, Countries, United States, China, Canada, Mexico, States, New York,  California, Florida, Texas, Cities, New York City, Long Beach, Houston, Miami, Tampa, Orlando. Assuming every topic has the category "Continents", the page would then display all categories. If I clicked on Florida, I would only see the categories "Orlando, Tampa, Miami". I've written my query as such:

SELECT DISTINCT fcat.id, fcat.name FROM topic_category AS cat 
JOIN topic_category_bridge AS bridge ON cat.id = bridge.category_id 
JOIN topic AS topic ON bridge.topic_id = topic.id 
JOIN topic_category_bridge AS sbridge ON topic.id = sbridge.topic_id 
JOIN topic_category AS fcat ON sbridge.category_id = fcat.id 
WHERE cat.id = 2 AND cat.id = 19 AND cat.id = 46 
ORDER BY fcat.name ASC

Open in new window


What am I doing wrong? Thanks for the help in advance!
Anthony NegronAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ares KurkluSoftware EngineerCommented:
Doing and makes no sense as the id can not be 19 and 2 at the same time

SELECT DISTINCT fcat.id, fcat.name FROM topic_category AS cat 
JOIN topic_category_bridge AS bridge ON cat.id = bridge.category_id 
JOIN topic AS topic ON bridge.topic_id = topic.id 
JOIN topic_category_bridge AS sbridge ON topic.id = sbridge.topic_id 
JOIN topic_category AS fcat ON sbridge.category_id = fcat.id 
WHERE (cat.id = 2 or cat.id = 19 or cat.id = 46 )
ORDER BY fcat.name ASC

Open in new window

or you can use cat.id in (2,19,46)
1
Anthony NegronAuthor Commented:
Doing this wouldn't filter the categories though?
0
NerdsOfTechTechnology ScientistCommented:
It sounds like you need another column `ParentID` or, alternatively, a one-to-many (1:M) table that is responsible for maintaining the relationship between parent and child categories' IDs.

For the former, you would have a column parentID that reflects the parent catID of the child category. In this case, mining down would just need the parentID (as an INPUT such as a $_POST variable) in the WHERE clause:

SELECT DISTINCT fcat.id, fcat.name FROM topic_category AS cat 
JOIN topic_category_bridge AS bridge ON cat.id = bridge.category_id 
JOIN topic AS topic ON bridge.topic_id = topic.id 
JOIN topic_category_bridge AS sbridge ON topic.id = sbridge.topic_id 
JOIN topic_category AS fcat ON sbridge.category_id = fcat.id 
WHERE (cat.parentid = 19) /* lists all categories which have a parentID of 19 */
ORDER BY fcat.name ASC

Open in new window


Note: You probably would also do a count(*) query below, before the above query for the parent. Also, you would run the count query, for each child, before you output the child records, to either link the child info if it has children IDs of its own, so that the user can mine down further, or spit out a plain output for child ID(s) that have no children records.

SELECT count(*) FROM topic_category AS cat 
WHERE (cat.parentid = 19)

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
NerdsOfTechTechnology ScientistCommented:
Adding a column to create a SELF-JOINable 'parentID' or a separate table which maintains such a 'tree' should help the OP achieve the heirarchy design desired.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.