RELATIONAL DATABASE troubles. I feel dumb! :-)

Results 1 to 4 of 4

Thread: RELATIONAL DATABASE troubles. I feel dumb! :-)

  1. #1
    Tom Levesque Guest

    Default RELATIONAL DATABASE troubles. I feel dumb! :-)

    Okay, here goes.<BR><BR>I&#039;m working with three related tables:<BR><BR>Links<BR>SubCategories<BR>Categorie s<BR><BR>Each link has a foreign key to the sub category it belongs to, and each sub category has a foreign key to the category it belongs to. <BR><BR>So for example on a particular link I might have<BR><BR>Sub_Category_ID = 1 &#039; or whatever<BR><BR>And then on that sub category I&#039;d have<BR><BR>Category_ID = 5 &#039; or whatever<BR><BR>This is all good and fine. But lets say that I want to select all the links in a particular CATEGORY. Since the links have no direct relation to the CATEGORIES table, is there no good way of doing this?<BR><BR>For example, if I wanted all the links in SUBCATEGORY "1" all I would have to do is this<BR><BR>SELECT * FROM Links WHERE SUBCATEGORY = &#039;1&#039;;<BR><BR>But if I wanted all the links in a CATEGORY I&#039;d have to say<BR><BR>rs = "SELECT * FROM SUBCATEGORIES WHERE CATEGORIES = 5"<BR><BR>and then<BR><BR>rs2 = "SELECT * FROM LINKS WHERE SUBCATEGORY = " & rs("SUBCATEGORY_ID")<BR><BR>And even that doesn&#039;t work because then I&#039;m stuck with only the FIRST sub category in RS so I&#039;d have to loop through all the sub categories in RS and keep popping them into RS2 .......<BR><BR>UGH.........<BR><BR>Surely there is a better way to do this?

  2. #2
    Join Date
    Dec 1969

    Default SQL magic

    You can join two or more tables in one query<BR><BR>select l.*<BR> from links as l,<BR> subcategories as sc,<BR> categories as c<BR> where l.subcategory = sc.subcategoryid<BR> and sc.categories = c.categoryid<BR> and c.category = &#039;Fun & games&#039; <BR><BR>with the reservation that I do not know all column names in your tables.<BR><BR>The above query will retrieve information about links which belongs to any subcategory in the category named Fun & games<BR><BR>maybe you should check a tutorial on SQL. Here is one<BR><BR>

  3. #3
    Jay Seagrave Guest

    Default RE: RELATIONAL DATABASE troubles. I feel dumb! :-)

    how &#039;bout something like this: <BR><BR>select links.*,sub_category.ID from links, sub_category WHERE sub_category.ID = links.sub_catID AND sub_category.catID = 5

  4. #4
    Tom Levesque Guest

    Default Thanks Guys

    Thanks. My brain just wasn&#039;t developed enough to incorporate "WHERE" and "INNER JOIN" in a single SQL statement. <BR><BR>This is what I ended up with:<BR><BR>SELECT * FROM Links<BR>Inner Join SubCategories<BR>On Links.Sub_Category_Name = SubCategories.ID<BR>Where SubCategories.Category_Name = 1;<BR><BR>THANKS AGAIN!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts