Solved

SQL grouping two tables into another with a business name

Posted on 2016-09-28
9
53 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
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.

 

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 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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

815 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

8 Experts available now in Live!

Get 1:1 Help Now