• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 87
  • Last Modified:

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?
0
dannyg280
Asked:
dannyg280
  • 3
  • 3
  • 3
1 Solution
 
Terry WoodsIT GuruCommented:
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
 
Terry WoodsIT GuruCommented:
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
 
Ryan ChongCommented:
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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
dannyg280Author Commented:
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
 
Ryan ChongCommented:
>>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
 
dannyg280Author Commented:
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
 
Terry WoodsIT GuruCommented:
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
 
Ryan ChongCommented:
@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
 
dannyg280Author Commented:
That worked Terry! Thank you!!!!
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 3
  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now