Solved

MySQL Insert Combined IDs from 2 Unrelated tables

Posted on 2016-09-26
9
55 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 50

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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 

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

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 50

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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MS Access - need to reduce row size 25 51
Gettg error - Please help Msg 252, Level 16, State 1, Line 1 3 28
Help with query 3 23
SQL: launch actions one before the other 10 22
Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
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 …
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…

813 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

10 Experts available now in Live!

Get 1:1 Help Now