?
Solved

SQL grouping two tables into another with a business name

Posted on 2016-09-28
9
Medium Priority
?
72 Views
Last Modified: 2016-09-29
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');
0
Comment
Question by:rwallacej
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 3
9 Comments
 
LVL 44

Expert Comment

by:zephyr_hex (Megan)
ID: 41820687
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
 
LVL 35

Expert Comment

by:ste5an
ID: 41820694
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
 

Author Comment

by:rwallacej
ID: 41820713
hello, the join may work however this is not what is required, I need new table , not the query only
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:rwallacej
ID: 41820716
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
 
LVL 44

Accepted Solution

by:
zephyr_hex (Megan) earned 2000 total points
ID: 41820733
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
 
LVL 35

Expert Comment

by:ste5an
ID: 41820738
Well, there should be something left for you to do.. and it's really not that hard.
0
 
LVL 44

Expert Comment

by:zephyr_hex (Megan)
ID: 41820757
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
 
LVL 35

Expert Comment

by:ste5an
ID: 41821287
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
 

Author Closing Comment

by:rwallacej
ID: 41821313
Thanks for solution and comments
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question