asked on 9/26/2016

MySQL Insert Combined IDs from 2 Unrelated tables

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?

MySQL Server SQL

Last Comment

dannyg280

9/28/2016

Terry Woods

9/26/2016

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.

Terry Woods

9/26/2016

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.

Ryan Chong

9/27/2016

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?

dannyg280

9/27/2016

ASKER

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.

Ryan Chong

9/27/2016

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

dannyg280

9/27/2016

ASKER

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.

ASKER CERTIFIED SOLUTION

Terry Woods

9/27/2016

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

Ryan Chong

9/28/2016

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

dannyg280

9/28/2016

ASKER

That worked Terry! Thank you!!!!