SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Evangelist
    Join Date
    Apr 2007
    Posts
    398
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Querying two tables and certain columns?

    Hi, wondering if anyone can kindly help please

    After some help with a business search I have on a business directory. In the search you can either search for a company name ie:

    Gregs Bar & Restaurant

    Or a Business type ie:

    Fast Food

    And in my database I have a businesses table:



    And a categories table:



    So what I want to do is run a query which will query these two tables which I do like so.

    Code:
    SELECT *
    FROM businesses
    WHERE businesses.name = 'Fast Food'
    UNION
    SELECT *
    FROM categories
    WHERE  categories.name = 'Fast Food'
    However, where I'm stuck is where a user searches for a category ie: Fast Food. For example when Fast Food is searched for I also want results returned for:

    - Take Away – as it falls into the same category as fast food
    - Quick Bites – as it falls into the same category as fast food

    (As they are all kind of the same thing)

    So for example I want my search to :

    1) Query the two tables for what the search term ie: Fast Food
    When it finds Fast Food in the categories table look in the also_falls_into_category column of this table for Fast Food (piped separated)

    2) Then explode on the pipes and continue with the query and output all the businesses in the businesses table where any of the pipe separated values are matches in the falls_into_category column. So in effect when you search for fast food, you also get results returned for take away and quick bites.

    Can anyone help please?

    Thanks

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    your first problem occurred when you decided to store multiple id values in a single column

    this violates the first normal form of database design

    what you'll want to do is replace that column with an additional one-to-many table, so that each category is in its own row
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    Non-Member
    Join Date
    Feb 2005
    Posts
    737
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for your help. I've now normalized my database as shown:



    So a business can have many categories

    However I don't get how this would work now i''ve normalized the data. For example, take this data inserted into the two tables:



    Say someone searched for cafe then Bob's Cafe & Restaurant would be outputted (as it's category_id = 2), but you could argue that if a search for Take Away, or Fast Food was carried out then Bob's Cafe & Restaurant would also get the results returned (hence the pipes in my last example).

    How would I get round this, can't quite work it out in my head?

    Thanks

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    you didn't normalize it properly, sorry

    what you want is this --

    businesses
    business_id
    name

    categories
    category_id
    name

    business_categories
    business_id
    category_id

    each business can have multiple categories, so there could be multiple rows in the business_categories table for the same business

    each category can have multiple businesses, so there could be multiple rows in the business_categories table for the same category

    it's a classic many-to-many relationship
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    Non-Member
    Join Date
    Feb 2005
    Posts
    737
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Excellent - thank you for your help, understand now

    ps: defintely buying your book in the future! :-)


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
  •