Decreasing the number of results in a query

Question

Ok, so I have something like 2100 products in an MSAccess database, these products all go into multiple categories. I'm actually transferring this list to a new magento database. I have a query to get these specific products, but I'm getting ~17k records returned from the query, the huge amount of records being returned is definitely duplicates from the products appearing in multiple categories.

I really need those categories though so that I can re-categorize them in the new database.

Here is my sql:

 SELECT ProductCategory.CategoryID, Products.Code, Products.Name, Products.Keywords, Products.ImageLargePath, Products.Weight, Products.Price, Products.SalePrice, Products.Description, Products.ShortDescription
FROM Products INNER JOIN (Categories INNER JOIN ProductCategory ON Categories.uid = ProductCategory.CategoryID) ON Products.uid = ProductCategory.ProductID
WHERE ProductCategory.CategoryID IN (116, 40, 41, 46, 51, 101, 370, 519, 600, 701, 710, 722, 729, 736, 749, 761, 770, 778, 780, 781, 786, 788, 803, 804, 809, 812, 813, 914, 1097, 1219, 1277, 1320, 1398, 1399, 1400, 1403, 1537, 1742, 214, 215, 216, 217, 218, 219, 220, 221, 222, 223, 224, 225, 226, 227, 228, 230, 231, 232, 233, 234, 235, 236, 237, 239, 241, 242, 243, 244, 245, 246, 247, 248, 249, 252, 255, 256, 257, 258, 261, 262, 263, 270, 271, 272, 273, 274, 275, 276, 277, 278, 279, 280, 281, 282, 283, 284, 285, 287, 288, 289, 290, 294, 295, 297, 298, 299, 300, 301, 302, 303, 304, 305, 306, 307, 308, 309, 310, 311, 319, 320, 321, 322, 323, 324, 326, 327, 328, 329, 330, 331, 333, 334, 335, 343, 344, 345, 346, 347, 348, 349, 350, 351, 352, 353, 354, 355, 356, 357, 358, 359, 360, 361, 362, 363, 364, 365, 371, 373, 374, 375, 376, 377, 378, 379, 380, 381, 382, 383, 384, 385, 386, 387, 388, 389, 390, 391, 392, 393, 394, 395, 396, 397, 398, 399, 400, 401, 402, 403, 404, 405, 406, 407, 408, 409, 410, 411, 412, 413, 414, 415, 416, 417, 418, 419, 420, 421, 422, 423, 424, 425, 426, 427, 429, 430, 431, 435, 436, 437, 438, 439, 440, 441, 443, 444, 446, 450, 451, 452, 453, 454, 456, 457, 458, 459, 460, 462, 463, 464, 465, 466, 467, 468, 469, 470, 471, 472, 475, 477, 479, 480, 481, 482, 483, 484, 486, 487, 488, 489, 490, 491, 492, 493, 496, 498, 499, 500, 501, 502, 503, 504, 505, 506, 507, 508, 509, 510, 511, 512, 513, 514, 517, 518, 520, 521, 522, 523, 524, 525, 526, 527, 528, 529, 530, 531, 532, 533, 534, 537, 538, 539, 540, 541, 542, 543, 544, 545, 546, 547, 548, 549, 550, 551, 552, 553, 554, 555, 557, 558, 559, 560, 561, 562, 564, 565, 567, 568, 569, 570, 573, 575, 576, 577, 578, 579, 581, 582, 583, 584, 585, 589, 590, 591, 592, 593, 594, 596, 597, 599, 601, 602, 603, 604, 605, 606, 607, 608, 610, 611, 613, 614, 615, 616, 617, 619, 620, 621, 622, 623, 624, 625, 626, 627, 628, 629, 630, 631, 632, 633, 634, 636, 638, 639, 640, 641, 642, 643, 644, 646, 647, 648, 649, 651, 652, 653, 654, 655, 656, 657, 658, 659, 660, 661, 662, 663, 664, 665, 666, 670, 699, 702, 703, 704, 705, 706, 707, 708, 709, 711, 712, 713, 714, 715, 716, 717, 718, 719, 720, 721, 723, 724, 725, 726, 727, 728, 730, 731, 732, 733, 734, 735, 737, 738, 739, 740, 741, 742, 743, 744, 745, 746, 748, 750, 752, 753, 754, 755, 756, 757, 758, 759, 760, 762, 763, 764, 765, 766, 767, 768, 769, 771, 772, 773, 774, 775, 776, 777, 779, 782, 783, 784, 785, 787, 789, 790, 791, 792, 793, 794, 795, 796, 797, 798, 799, 800, 801, 802, 805, 806, 807, 808, 810, 811, 814, 815, 816, 817, 818, 819, 820, 821, 822, 823, 824, 825, 826, 827, 829, 830, 831, 834, 835, 836, 837, 838, 839, 840, 841, 843, 844, 845, 846, 847, 848, 852, 853, 855, 857, 858, 859, 864, 866, 867, 869, 870, 873, 874, 875, 876, 877, 878, 880, 882, 883, 884, 885, 886, 888, 889, 891, 892, 893, 894, 895, 897, 898, 900, 901, 902, 903, 904, 905, 906, 907, 908, 909, 910, 911, 912, 913, 915, 916, 917, 918, 919, 920, 921, 922, 923, 924, 925, 927, 928, 929, 930, 931, 932, 933, 934, 935, 936, 937, 938, 939, 940, 942, 943, 944, 946, 948, 952, 955, 956, 957, 958, 959, 960, 961, 962, 963, 964, 965, 966, 967, 968, 969, 970, 971, 972, 973, 975, 976, 977, 978, 979, 980, 981, 982, 983, 984, 985, 986, 88, 989, 990, 991, 992, 994, 996, 997, 998, 999, 1000, 1001, 1003, 1004, 1005, 1006, 1007, 1008, 1009, 1010, 1011, 1012, 1013, 1014, 1016, 1017, 1018, 1020, 1021, 1022, 1023, 1026, 1027, 1028, 1031, 1032, 1033, 1035, 1037, 1041, 1042, 1043, 1044, 1046, 1047, 1048, 1049, 1050, 1051, 1052, 1053, 1054, 1055, 1056, 1057, 1058, 1059, 1061, 1063, 1064, 1065, 1066, 1067, 1068, 1069, 1070, 1071, 1072, 1073, 1074, 1076, 1077, 1094, 1096, 1098, 1099, 1100, 1101, 1103, 1104, 1105, 1106, 1107, 1146, 1220, 1224, 1225, 1226, 1227, 1228, 1230, 1231, 1232, 1233, 1234, 1235, 1236, 1237, 1238, 1239, 1240, 1241, 1242, 1243, 1244, 1245, 1246, 1247, 1248, 1249, 1250, 1251, 1252, 1253, 1254, 1255, 1256, 1257, 1258, 1259, 1260, 1261, 1262, 1263, 1264, 1265, 1266, 1267, 1268, 1269, 1270, 1271, 1272, 1273, 1274, 1275, 1278, 1281, 1282, 1283, 1285, 1286, 1287, 1288, 1289, 1290, 1291, 1309, 1310, 1319, 1324, 1325, 1326, 1327, 1328, 1329, 1330, 1331, 1332, 1333, 1334, 1335, 1336, 1337, 1338, 1339, 1340, 1341, 1345, 1356, 1357, 1358, 1359, 1360, 1361, 1362, 1363, 1367, 1368, 1369, 1370, 1371, 1372, 1373, 1374, 1375, 1376, 1377, 1378, 1379, 1381, 1382, 1385, 1386, 1387, 1388, 1390, 1393, 1394, 1395, 1396, 1401, 1402, 1404, 1405, 1406, 1407, 1408, 1409, 1410, 1411, 1412, 1413, 1414, 1415, 1416, 1417, 1419, 1420, 1421, 1422, 1423, 1424, 1425, 1426, 1427, 1428, 1429, 1430, 1431, 1432, 1433, 1436, 1438, 1439, 1440, 1441, 1442, 1443, 1444, 1445, 1446, 1447, 1448, 1449, 1451, 1452, 1453, 1454, 1455, 1456, 1457, 1458, 1459, 1461, 1463, 1464, 1467, 1478, 1479, 1480, 1481, 1482, 1483, 1484, 1485, 1486, 1487, 1488, 1489, 1490, 1491, 1492, 1493, 1494, 1495, 1496, 1497, 1498, 1499, 1500, 1501, 1502, 1503, 1505, 1506, 1507, 1508, 1509, 1510, 1511, 1512, 1513, 1514, 1515, 1516, 1517, 1518, 1524, 1527, 1528, 1531, 1532, 1533, 1534, 1535, 1536, 1538, 1539, 1542, 1544, 1545, 1546, 1547, 1548, 1551, 1552, 1553, 1554, 1555, 1556, 1559, 1560, 1561, 1562, 1563, 1564, 1569, 1572, 1573, 1574, 1575, 1576, 1577, 1578, 1579, 1580, 1581, 1582, 1584, 1586, 1587, 1588, 1590, 1591, 1592, 1597, 1598, 1599, 1600, 1601, 1602, 1604, 1606, 1609, 1610, 1611, 1612, 1613, 1614, 1615, 1616, 1617, 1618, 1619, 1620, 1621, 1622, 1623, 1624, 1625, 1626, 1627, 1628, 1629, 1630, 1631, 1632, 1633, 1638, 1639, 1640, 1641, 1642, 1643, 1644, 1645, 1646, 1647, 1648, 1650, 1671, 1672, 1674, 1675, 1676, 1677, 1678, 1679, 1680, 1681, 1682, 1684, 1685, 1686, 1687, 1688, 1689, 1690, 1691, 1692, 1693, 1694, 1695, 1696, 1697, 1706, 1708, 1709, 1710, 1711, 1712, 1713, 1714, 1715, 1717, 1718, 1719, 1720, 1721, 1730, 1731, 1732, 1733, 1734, 1735, 1736, 1737, 1738, 1739, 1740, 1741, 1743, 1744, 1759)
ORDER BY ProductCategory.CategoryID;

Does anyone have any advice on maybe possibly merging the category ids in the results?

The biggest issue is tha the format for the csv to import products into the magento db lists multiple categoryID in one column i.e. [104,40,12] instead of listing just one at a time. I'd be fine sitting there and putting them all together, it's just that 17k results is apparently way too much for me to copy and paste into a csv, which I can then edit and import into magento.

Thanks for any help guys :)

Answer 1

Can't you just copy the products, categories and the links between them in a separate query?

What you do now, is flatten all the data. This does result in many more rows, but that makes sense, because products are in multiple categories, like you said.

I would very strongly advise against storing a separated list of ids in a single value. The current crosslink junction table you have in Access is much better. If you store multiple category ids in one column, there is no way you can build constraints or even normal joins. Bad idea, really.

That said, if you would need to do such a thing anyway, MySQL has the function GROUP_CONCAT, which lets you group values together in a single value. So you either need to copy the data to MySQL first, and then change the structure in MySQL, or you'll have to find an Access counterpart of that function. For that, start by reading in on this question.

Answer 2

Alternatively, you can send your tables and data to MySQL directly from Access, for example in Access 2013, you can go to External Data->Export->More->ODBC Database and follow the wizard, just note that you need to have an ODBC connection to your MySQL database created beforehand.