SQL grouping two tables into another with a business name

this isn't real example of scenario but will hopefully help to solve
I have two tables, one a list of businesses and the other a list of categories.  businesses are of 1 or more categories.  I need to create 3rd table matching business with category, I need todo this automatically based on business name/part business name string

example is "An Indian Restaurant"  is of category "Indian"
business "Chinese and Indian" is of category both "Indian" and "Chinese"

please implement solution for it , I imagine a stored procedure with FOR loops?

please try script below to create 3 tables.  in real life I want to fill data in the 3rd table Business_Food_Types automatically. this is the part that needs implemented
I don't want to duplicate the rows in Business_Food_Types


Food_Categories_Types
1      Indian
2      Chinese
3      British

Master_BusinessNames
1      An Indian restaurant
2      A Chinese takeaway
3      Local Chinese
4      Local British
5      British Sandwich
6      Chinese and Indian

Business_Food_Types
1      1      1
2      2      2
3      3      2
4      4      3
5      5      3
6      6      1
7      6      2

SCRIPT TO CREATE TABLES AND VALUES

--a list of food categories
--drop table Food_Categories_Types
CREATE TABLE Food_Categories_Types
(
      foodcategory_ID int,
      foodcategoryType varchar(100)
)

insert into Food_Categories_Types values ('1','Indian');
insert into Food_Categories_Types values ('2','Chinese');
insert into Food_Categories_Types values ('3','British');
SELECT * FROM Food_Categories_Types

--a list of business names
--drop TABLE Master_BusinessNames
CREATE TABLE Master_BusinessNames
(
      masterbusinessID int,
      masterbusinessName varchar(100)
)
insert into Master_BusinessNames values ('1','An Indian restaurant');
insert into Master_BusinessNames values ('2','A Chinese takeaway');
insert into Master_BusinessNames values ('3','Local Chinese');
insert into Master_BusinessNames values ('4','Local British');
insert into Master_BusinessNames values ('5','British Sandwich');
insert into Master_BusinessNames values ('6','Chinese and Indian');
SELECT * FROM Master_BusinessNames





--*****this is the table that needs data filled in automatically, based on business name, there must not be duplicates though*****
--drop table Business_Food_Types
select * from Business_Food_Types
--drop TABLE Business_Food_Types
CREATE TABLE Business_Food_Types
(
      business_food_types_id int,
      business_id int,
      foodcategory_id int
)

insert into Business_Food_Types(business_food_types_id, business_id,foodcategory_id)
values ('1','1','1');
insert into Business_Food_Types(business_food_types_id, business_id,foodcategory_id)
values ('2','2','2');
insert into Business_Food_Types(business_food_types_id, business_id,foodcategory_id)
values ('3','3','2');
insert into Business_Food_Types(business_food_types_id, business_id,foodcategory_id)
values ('4','4','3');
insert into Business_Food_Types(business_food_types_id, business_id,foodcategory_id)
values ('5','5','3');
insert into Business_Food_Types(business_food_types_id, business_id,foodcategory_id)
values ('6','6','1');
insert into Business_Food_Types(business_food_types_id, business_id,foodcategory_id)
values ('7','6','2');
rwallacejAsked:
Who is Participating?
 
zephyr_hex (Megan)Connect With a Mentor DeveloperCommented:
If you've already inserted some data in business_food_types, then just exclude those records from your query, like so:

INSERT INTO Business_Food_Types (business_id,foodcategory_id )
SELECT b.business_id,f.foodcategory_id
FROM Master_BusinessNames b
INNER JOIN Food_Categories_Types f ON b.masterbusinessName LIKE  '%' + f.foodcategoryType + '%'
LEFT OUTER JOIN Business_Food_Types bf ON bf.business_id = b.business_id AND bf.foodcatgory_id = f.foodcategory_id
WHERE bf.business_food_types_id IS NULL

Open in new window


And hopefully these aren't production tables.  Manually entering in record id's is prone to error.
0
 
zephyr_hex (Megan)DeveloperCommented:
There's no need for loops here.  A simple INSERT with SELECT will do the trick.  But first, you should create your tables with IDENTITY (1,1) INT PRIMARY KEY NOT NULL columns for the id, so SQL manages the increments instead of you (because SQL will handle it better).  This also makes your insert easier.

Please test the following by running the SELECT portion of the statement without the INSERT, to verify the results are correct.  I did not test this.

INSERT INTO Business_Food_Types (business_id,foodcategory_id )
SELECT b.business_id,f.foodcategory_id
FROM Master_BusinessNames b
INNER JOIN Food_Categories_Types f ON b.masterbusinessName LIKE  '%' + f.foodcategoryType + '%'

Open in new window


You should also consider placing foreign key constraints on Business_Food_Types so that it only allows ID's that exist in the other tables.
0
 
ste5anSenior DeveloperCommented:
Use a non-equi join with LIKE. E.g.

DECLARE @Food_Categories_Types TABLE
    (
      foodcategory_ID INT ,
      foodcategoryType VARCHAR(100)
    )

INSERT  INTO @Food_Categories_Types
VALUES  ( '1', 'Indian' ),
        ( '2', 'Chinese' ),
        ( '3', 'British' );

DECLARE @Master_BusinessNames TABLE
    (
      masterbusinessID INT ,
      masterbusinessName VARCHAR(100)
    )
INSERT  INTO @Master_BusinessNames
VALUES  ( '1', 'An Indian restaurant' ),
        ( '2', 'A Chinese takeaway' ),
        ( '3', 'Local Chinese' ),
        ( '4', 'Local British' ),
        ( '5', 'British Sandwich' ),
        ( '6', 'Chinese and Indian' );

SELECT	*
FROM	@Master_BusinessNames M
	LEFT JOIN @Food_Categories_Types T ON M.masterbusinessName LIKE '%' + T.foodcategoryType + '%';

SELECT	M.masterbusinessID,
		T.foodcategory_ID
FROM	@Master_BusinessNames M
	INNER JOIN @Food_Categories_Types T ON M.masterbusinessName LIKE '%' + T.foodcategoryType + '%';

Open in new window

0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
rwallacejAuthor Commented:
hello, the join may work however this is not what is required, I need new table , not the query only
0
 
rwallacejAuthor Commented:
zephyr_hex - how does this handle avoiding duplicates in Business_Food_Types?  some "mapping" has already been done in Business_Food_Types manually
I appreciate comments on limits etc. but I'm unable to change database structure
0
 
ste5anSenior DeveloperCommented:
Well, there should be something left for you to do.. and it's really not that hard.
0
 
zephyr_hex (Megan)DeveloperCommented:
Oh wait.  Since Business_Food_Types does not have an IDENTITY based id, you'll have to use something like a CTE to generate the next id.  I'm not going to do that for you because I just can't bring myself to show someone how to do something that's a horrible idea.

Go back to whoever designed these tables and help them to understand why the id columns simply can not be generated with the current method that's being used.  There are no safeguards to guarantee what number should be used next when inserting records.  You WILL end up with duplicates due to race conditions.  Well, unless only 1 person ever uses this at a time, which is not a good design assumption.
0
 
ste5anSenior DeveloperCommented:
btw, there is normally no need for an additional key (business_food_types_id ) in  Business_Food_Types. Just use the natural key.

But when you need an artificial key, the table should provide it by using IDENTITY or SEQUENCE. Manually creating an artificial key is in most scenarios a bad idea. Cause its lead to overloading it.
0
 
rwallacejAuthor Commented:
Thanks for solution and comments
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.