Querying two tables and certain columns?

Databases
1

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.


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

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

3

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

you didn’t normalize it properly, sorry :slight_smile:

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

5

Excellent - thank you for your help, understand now

ps: defintely buying your book in the future! :slight_smile: