mgm_03 Posted November 18, 2003 Share Posted November 18, 2003 In my application, there will be a few hundred different service categories and in some cases, sub-categories. Since any company can offer one or more services and sub-cats, logically, this means a many to many relationship with a mapping table. However, I would like to maintain a separate table for each category because the idea of having, say, 300 categories and subcats all in one mapping table just feels like the wrong way to do it. If I want to retrieve all the services company A offers, How do I write a SELECT statement that iterates through every service category table checking for the presence of company A\'s unique ID? Any advice is appreciated !! Quote Link to comment Share on other sites More sharing options...
Barand Posted November 18, 2003 Share Posted November 18, 2003 However, I would like to maintain a separate table for each category Only one word to say about that - DON\'T, or you\'ll have to do several hundred separate queries. Category ----------- catid category Subcat -------- subcatid catid subcategory Service ---------- subcatid companyid Company ----------- companyid companyname etc [php:1:3daf3f8861]<?php $cid = $searchid; $sql = \"select c.category, sc.subcategory FROM (service s INNER JOIN subcategory sc ON s.subcatid = sc.subcatid) INNER JOIN category c ON sc.catid = c.catid WHERE s.companyid = $cid\"; ?>[/php:1:3daf3f8861] Quote Link to comment Share on other sites More sharing options...
mgm_03 Posted November 19, 2003 Author Share Posted November 19, 2003 hey man! thanks...I would\'ve never thought to do this in a jillion years. I can\'t honestly say I understand it (being a dumbass noobie and all) but I\'ll read up on MySQL to understand the relations you defined in the query. I don\'t suppose this query would also accomodate situations in which a category has no subcategory at all? I wonder if in those instances, I could just make a table for a category and subcategory with one entry in each table. thanks again!! Quote Link to comment Share on other sites More sharing options...
Barand Posted November 19, 2003 Share Posted November 19, 2003 You are right, in the model each cat must have at least 1 subcat so you would need cat=widget, subcat = widget where a cat had no subcats. The services gives the many to many relation between company and subcat, letting you search for those companys offering a particular category or, conversely, the cats offered by a company, as above. Quote Link to comment Share on other sites More sharing options...
mgm_03 Posted November 19, 2003 Author Share Posted November 19, 2003 I appreciate the help and would be willing to offer a donation if you have Paypal. Alot of people on this board are very generous with their expertise but this particular solution solves a huge puzzle for me. My database directory is for finding machine shops. In addition to offering many services, they also work with many types of materials. So, again, I have a situation of Cats (metals)(plastics)(etc) and Subcats(steel, copper, brass, etc.) ( ) ( ) It appears to be an identical table setup as we just reviewed. To retrieve all the Services and Materials a company offers, my initial thought is to concatenate both query statements with an \'AND\'. Or, just have 2 distinct queries. Thanks, Mark P.S. -------------------- If you can write a script that can remove G.Bush from the White House, you\'d be doing Planet Earth a huge favor!!! Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.