Solved

MySQL Insert Combined IDs from 2 Unrelated tables

Posted on 2016-09-26
9
42 Views
Last Modified: 2016-09-28
I have 2 unrelated tables "customers" and "categories"
customers has 2 fields custID and custName
categories has 2 fields catID and CatName

I have a 3rd table custcats that I want to insert Data into. I want to insert a unique ID into custcats that is custID combined with catID.

So if custID was 5050 and catID was 99 I would want the custcatID to be 505099. I want one row inserted for each combination of custID and CatID.

How would I go about achieving this?
0
Comment
Question by:dannyg280
  • 3
  • 3
  • 3
9 Comments
 
LVL 35

Expert Comment

by:Terry Woods
ID: 41816645
The best way for performance is to use two columns, and set the primary key to include both columns. That way indexing and joins are efficient, and queries are easy to write.

Concatenating data is terribly inefficient when it comes to joining tables.
0
 
LVL 35

Expert Comment

by:Terry Woods
ID: 41816702
Appending the two numbers without a separator will also mean you can't tell whether 505099 is for custID 5050 and catID 99 or for custID 50 and catID 5099.

You could get around this perhaps by using (for example) the first 6 digits for custID and the next 4 for catID, but you'd have to ensure your custID's were always at least 6 digits or you run into the previous problem.

Any more than 9 digits in total would require a BIGINT data type.

It would still be inefficient though, as it still prevents the database from joining efficiently.
0
 
LVL 49

Expert Comment

by:Ryan Chong
ID: 41817138
why you need a combine key for table: custcats?

why not just make fields: custID and catID in table: custcats as a composite key?

also thing to consider, will one customer has more than one category?
0
 

Author Comment

by:dannyg280
ID: 41817706
Hi Terry and Ryan. I do get what both are you are saying, it would make more sense to just include a custID and catID field in the Custcats table.

also thing to consider, will one customer has more than one category?

Yes! And that becomes my issue. There are about 2,000 customers and only about 25 categories, but I need a row for each category under each customer... so 50,000 rows.

If someone could help me with how to write a query that could pull that data from the other 2 tables and insert it I can could just use an auto increment key for the custcats.
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 49

Expert Comment

by:Ryan Chong
ID: 41817866
>>There are about 2,000 customers and only about 25 categories, but I need a row for each category under each customer... so 50,000 rows.

is that a probability or you are saying it's fixed that it's always 2000 x 25 = 50000 rows?

if yes, that always can be done as simple as writing a Insert query, but what if there are more customers or categories to be added in the future, how you want the records in table: Custcats to be handled?
0
 

Author Comment

by:dannyg280
ID: 41817948
Yes, in this case it is fixed, we need the custcat table to contain one row for each customer and category. Those two tables will not change.

So there would be 25 rows for every custID, each containing a different catID. I hope I'm explaining that correctly. I just do not know how to write the Insert to pull the data that way.
0
 
LVL 35

Accepted Solution

by:
Terry Woods earned 500 total points
ID: 41818443
This should do it. It's known as a "full outer join" as we're querying from multiple tables but without joining them:
insert into custcat (custID, catID)
select custID, catID
from customers, categories

Open in new window

0
 
LVL 49

Expert Comment

by:Ryan Chong
ID: 41819007
@dannyg280,

what suggested by Terry in ID: 41818443 should do the trick to insert all permutation into your target table.

as mentioned, so you got to handle the scenario for new customer or category in future (if that happened)
0
 

Author Closing Comment

by:dannyg280
ID: 41819610
That worked Terry! Thank you!!!!
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

759 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

20 Experts available now in Live!

Get 1:1 Help Now