SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    With More ! for your $ maxor's Avatar
    Join Date
    Feb 2004
    Location
    Scottsdale, Arizona
    Posts
    909
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Help with JOIN and linking two tables.

    I've got a site that I'm developing that stores FAQs about cars.

    There is an FAQ table which stores basic information about an FAQ (ID, Title, Difficulty and Text).

    There is also a table for the different categories (Engine, Suspension, Brakes etc.). Each row in this table has an ID and a Label (1, Engine)

    I have a table linking those to called FAQ_CAT which has two indexes (faqID and catID).

    I am confused about how to write the SQL for this.

    If I wanted to find all of the FAQs that were about category ID 1, Engine, I could use.
    Code:
    SELECT * FROM FAQ_CAT
    WHERE catID = 1;
    However this SQL will only display the items in the FAQ_CAT table. What I need is SQL that will find all of the FAQs for a particular category, and then display the information from the FAQ table.

    Any suggestions?

  2. #2
    SitePoint Enthusiast
    Join Date
    Apr 2004
    Location
    TX
    Posts
    71
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    What DBMS are you using??....ACCESS??

    Since you're only using 2 tables and what you want is relatively limited, both an inner join or just using a sub query would work...try this...

    Using an inner join...
    Code:
    Select  ID, Title, Difficulty, Text from FAQ F
    inner   join FAQ_CAT FC on F.ID = FC.FaqID
    where   FC.CatID = 1
    ...or you could just use a sub query...
    Code:
    Select  * from FAQ
    where   ID in
              (Select FaqID from Faq_Cat
               where  CatID = 1)
    ...most would probably agree that the join is the cleaner way to do it, w/your specific need though, both get the job done. Is this what you were looking for? If for some reason you want to go to the Category table, you're gonna wind up needing to join on that table too. Not sure what the exact table structures are, or if they'll change, but for your specific example, the above would work, be careful if you start elaborating that query, you may wind up needing to fully qualify the SELECT fields when using the joins (b/c I didn't in the code I gave you).

  3. #3
    With More ! for your $ maxor's Avatar
    Join Date
    Feb 2004
    Location
    Scottsdale, Arizona
    Posts
    909
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    No I think that answers my question. Thank you.

    However at this point I think it makes more sense to include catID, genID and modID as foreign keys in the FAQ table since every FAQ will have each of these attributes.

    Thanks for the help, I may end up using that.

  4. #4
    SitePoint Enthusiast
    Join Date
    Apr 2004
    Location
    TX
    Posts
    71
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    cool. Not sure how those other fields are organized in your tables, but if you run into probs post up and we'll help you out.

  5. #5
    With More ! for your $ maxor's Avatar
    Join Date
    Feb 2004
    Location
    Scottsdale, Arizona
    Posts
    909
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well scratch that idea about foreign keys, it limits my flexibility.

    Thanks for the help Null, that did the trick!


Bookmarks

Posting Permissions

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