Solved

MySQL Insert Combined IDs from 2 Unrelated tables

Posted on 2016-09-26
9
71 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 52

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
Upcoming Webinar: Securing your MySQL/MariaDB data

Join Percona’s Chief Evangelist, Colin Charles as he presents Securing your MySQL®/MariaDB® data on Tuesday, July 11, 2017 at 7:00 am PDT / 10:00 am EDT (UTC-7).

 

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 52

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 52

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

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

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

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…
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

696 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