Solved

SQL grouping two tables into another with a business name

Posted on 2016-09-28
9
48 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
  • 3
  • 3
  • 3
9 Comments
 
LVL 42

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 33

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
 

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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 42

Accepted Solution

by:
zephyr_hex (Megan) earned 500 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 33

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 42

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 33

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

895 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now