Solved

SQL grouping two tables into another with a business name

Posted on 2016-09-28
9
41 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
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 32

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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 42

Accepted Solution

by:
zephyr_hex 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 32

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

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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
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.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

762 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

19 Experts available now in Live!

Get 1:1 Help Now