CODEWITHSUNDEEP
phpmysqldatabasejoin
Daansk44
Daansk44

Reputation: 497

Writing a double join to get the subcategory of the product

Making a double join to sort out the subcategory

I am writing a join to grab the subcategory from another table. But the problem that I have is that the subcategory does not have a unique id. The subcatogory is made from the xncatcode and the catid together. They are both in the sub_cattable. When you look in the producten table you see xngroupcode (cat) and xncatdesc (subcat).

The problem I am now facing is that I get the right productgroup back. But the wrong sub_cat. seen I join them both separately (I think)

enter image description here

SELECT DISTINCT
    * 
FROM 
    froets.producten
    left JOIN  froets.productcategorieën as PGROUP 
        on froets.PGROUP.productcategorienummer = froets.producten.xngroupcode 
    left JOIN  froets.sub_cat as PCAT
    on froets.producten.xncatncode = froets.PCAT.subcatid 
    left JOIN  froets.sub_cat as SCAT 
        on froets.SCAT.catid = froets.PGROUP.productcategorienummer 
WHERE 
    froets.producten.itemdesc LIKE '%flessen%'
    or froets.producten.brand like '%flessen%'

Extra data :)

In the producten table are all products included the xncatcode and the xngroupcode (these are both in the sub_cat). So is there any possibility to join them there.

# id, model, itemcode, itemdesc, extdesc, itemkeywords, size, sizegrid, gender, weightgr, lengthcm, heightcm, widthcm, diametercm, sizecombined, qtypercarton, decopackagingindiv, decopackagingindivtype, decopackaging, grossweightkg, nettweightkg, exportlcm, exportwcm, exporthcm, countryoforigin, hscode, impallmethods, impmethoddefault, impallpositions, imppositiondefault, imppositionsimpledefault, impwidthdefaultmm, impheightdefaultmm, impdiameterdefaultmm, impsizedefaultmm, maxcoloursdefault, brand, xngroupcode, xngroupdesc, xncatncode, xncatdesc, colordesc, simplecolor, pmscolorreference, basiccolor, peninkcolor, material, batterytype, features, bestseller, eancode, thematicitem, compliances, imagemain, imagedecoy1, imagedecoy2, imagedecoy3, imagepackage, imagefront, imageback, imageextra1, imageextra2, imageextra3, imagedetail1, imagedetail2, imagedetail3, imageprintlinesdefault, marksegment, maincat, eoycat, launchdate, language, hexcolor, imagegroup, imagemood1, imagemood2, imagemood3, imagemodel, videourl1, videourl2, newitem, id, productcategorienaam, productcategorienummer, productcategoriegroep, GroepAfbeelding, id, naam, subcatid, catid, id, naam, subcatid, catid
'44870', '104231', '10423100', 'zaklamp flessenopener', 'Omega zaklamp. Aluminium. ', 'Lamp, lampen, zaklamp, zaklampen, lantaarn, lantaarns, zaklantaarn, zaklantaarns, veiligheidslamp, veiligheidslampen,', '', '', 'None', '48', '', '10', '', '2,5', '10 x Ø 2,5 cm', '200', 'No', '', '10 / Joined bubble bag', '12', '11,4', '32', '34', '24', 'China (volksrepubliek)', '8513100000000000000000', 'Tampondruk, Lasergraveren', 'Tampondruk', 'romp', 'Lamp left, button top,On colored part', 'romp', '35', '10', '', '35 x 10 mm', '4', 'Bullet', '12', 'Tools & Torches', '1', 'Zaklampen', 'Gun metal', 'Midden grijs', '', 'Grijs', '', 'Aluminium.', '', '', '', '8713159202448', 'GUNMETAL', 'Batterijrichtlijn, WEEE, RoHS , Getest op cadmium ', '10423100.jpg', '10423100_PP_Y1.jpg', '', '', '', '10423100_F1.jpg', '', '', '10423100_E2.jpg', '', '', '', '', '10423100_1_1297_1927.jpg', 'Bullet', 'N', 'N', 'JAN16', 'NL', '6F6E71', '', '', '', '', '', '', '', 'false', '20', 'Tools & Torches', '12', NULL, '', '1', 'Sweat Shirts', '1', '21', '20', 'Auto accessoires', '3', '12'
'44870', '104231', '10423100', 'zaklamp flessenopener', 'Omega zaklamp Aluminium. ', 'Lamp, lampen, zaklamp, zaklampen, lantaarn, lantaarns, zaklantaarn, zaklantaarns, veiligheidslamp, veiligheidslampen,', '', '', 'None', '48', '', '10', '', '2,5', '10 x Ø 2,5 cm', '200', 'No', '', '10 / Joined bubble bag', '12', '11,4', '32', '34', '24', 'China (volksrepubliek)', '8513100000000000000000', 'Tampondruk, Lasergraveren', 'Tampondruk', 'romp', 'Lamp left, button top,On colored part', 'romp', '35', '10', '', '35 x 10 mm', '4', 'Bullet', '12', 'Tools & Torches', '1', 'Zaklampen', 'Gun metal', 'Midden grijs', '', 'Grijs', '', 'Aluminium.', '', '', '', '8713159202448', 'GUNMETAL', 'Batterijrichtlijn, WEEE, RoHS , Getest op cadmium ', '10423100.jpg', '10423100_PP_Y1.jpg', '', '', '', '10423100_F1.jpg', '', '', '10423100_E2.jpg', '', '', '', '', '10423100_1_1297_1927.jpg', 'Bullet', 'N', 'N', 'JAN16', 'NL', '6F6E71', '', '', '', '', '', '', '', 'false', '20', 'Tools & Torches', '12', NULL, '', '1', 'Sweat Shirts', '1', '21', '33', 'Gereedschapssetjes', '2', '12'
'44870', '104231', '10423100', 'zaklamp flessenopener', 'Omega zaklamp  Aluminium. ', 'Lamp, lampen, zaklamp, zaklampen, lantaarn, lantaarns, zaklantaarn, zaklantaarns, veiligheidslamp, veiligheidslampen,', '', '', 'None', '48', '', '10', '', '2,5', '10 x Ø 2,5 cm', '200', 'No', '', '10 / Joined bubble bag', '12', '11,4', '32', '34', '24', 'China (volksrepubliek)', '8513100000000000000000', 'Tampondruk, Lasergraveren', 'Tampondruk', 'romp', 'Lamp left, button top,On colored part', 'romp', '35', '10', '', '35 x 10 mm', '4', 'Bullet', '12', 'Tools & Torches', '1', 'Zaklampen', 'Gun metal', 'Midden grijs', '', 'Grijs', '', 'Aluminium.', '', '', '', '8713159202448', 'GUNMETAL', 'Batterijrichtlijn, WEEE, RoHS , Getest op cadmium ', '10423100.jpg', '10423100_PP_Y1.jpg', '', '', '', '10423100_F1.jpg', '', '', '10423100_E2.jpg', '', '', '', '', '10423100_1_1297_1927.jpg', 'Bullet', 'N', 'N', 'JAN16', 'NL', '6F6E71', '', '', '', '', '', '', '', 'false', '20', 'Tools & Torches', '12', NULL, '', '1', 'Sweat Shirts', '1', '21', '47', 'Zakmes', '4', '12'
'44870', '104231', '10423100', 'zaklamp flessenopener', 'Omega zaklamp Aluminium. ', 'Lamp, lampen, zaklamp, zaklampen, lantaarn, lantaarns, zaklantaarn, zaklantaarns, veiligheidslamp, veiligheidslampen,', '', '', 'None', '48', '', '10', '', '2,5', '10 x Ø 2,5 cm', '200', 'No', '', '10 / Joined bubble bag', '12', '11,4', '32', '34', '24', 'China (volksrepubliek)', '8513100000000000000000', 'Tampondruk, Lasergraveren', 'Tampondruk', 'romp', 'Lamp left, button top,On colored part', 'romp', '35', '10', '', '35 x 10 mm', '4', 'Bullet', '12', 'Tools & Torches', '1', 'Zaklampen', 'Gun metal', 'Midden grijs', '', 'Grijs', '', 'Aluminium.', '', '', '', '8713159202448', 'GUNMETAL', 'Batterijrichtlijn, WEEE, RoHS , Getest op cadmium ', '10423100.jpg', '10423100_PP_Y1.jpg', '', '', '', '10423100_F1.jpg', '', '', '10423100_E2.jpg', '', '', '', '', '10423100_1_1297_1927.jpg', 'Bullet', 'N', 'N', 'JAN16', 'NL', '6F6E71', '', '', '', '', '', '', '', 'false', '20', 'Tools & Torches', '12', NULL, '', '1', 'Sweat Shirts', '1', '21', '48', 'Zaklampen', '1', '12'
'44870', '104231', '10423100', 'zaklamp flessenopener', 'Omega zaklamp Aluminium. ', 'Lamp, lampen, zaklamp, zaklampen, lantaarn, lantaarns, zaklantaarn, zaklantaarns, veiligheidslamp, veiligheidslampen,', '', '', 'None', '48', '', '10', '', '2,5', '10 x Ø 2,5 cm', '200', 'No', '', '10 / Joined bubble bag', '12', '11,4', '32', '34', '24', 'China (volksrepubliek)', '8513100000000000000000', 'Tampondruk, Lasergraveren', 'Tampondruk', 'romp', 'Lamp left, button top,On colored part', 'romp', '35', '10', '', '35 x 10 mm', '4', 'Bullet', '12', 'Tools & Torches', '1', 'Zaklampen', 'Gun metal', 'Midden grijs', '', 'Grijs', '', 'Aluminium.', '', '', '', '8713159202448', 'GUNMETAL', 'Batterijrichtlijn, WEEE, RoHS , Getest op cadmium ', '10423100.jpg', '10423100_PP_Y1.jpg', '', '', '', '10423100_F1.jpg', '', '', '10423100_E2.jpg', '', '', '', '', '10423100_1_1297_1927.jpg', 'Bullet', 'N', 'N', 'JAN16', 'NL', '6F6E71', '', '', '', '', '', '', '', 'false', '20', 'Tools & Torches', '12', NULL, '', '1', 'Sweat Shirts', '1', '21', '49', 'Meetlinten', '8', '12'
'44870', '104231', '10423100', 'zaklamp flessenopener', 'Omega zaklamp  Aluminium. ', 'Lamp, lampen, zaklamp, zaklampen, lantaarn, lantaarns, zaklantaarn, zaklantaarns, veiligheidslamp, veiligheidslampen,', '', '', 'None', '48', '', '10', '', '2,5', '10 x Ø 2,5 cm', '200', 'No', '', '10 / Joined bubble bag', '12', '11,4', '32', '34', '24', 'China (volksrepubliek)', '8513100000000000000000', 'Tampondruk, Lasergraveren', 'Tampondruk', 'romp', 'Lamp left, button top,On colored part', 'romp', '35', '10', '', '35 x 10 mm', '4', 'Bullet', '12', 'Tools & Torches', '1', 'Zaklampen', 'Gun metal', 'Midden grijs', '', 'Grijs', '', 'Aluminium.', '', '', '', '8713159202448', 'GUNMETAL', 'Batterijrichtlijn, WEEE, RoHS , Getest op cadmium ', '10423100.jpg', '10423100_PP_Y1.jpg', '', '', '', '10423100_F1.jpg', '', '', '10423100_E2.jpg', '', '', '', '', '10423100_1_1297_1927.jpg', 'Bullet', 'N', 'N', 'JAN16', 'NL', '6F6E71', '', '', '', '', '', '', '', 'false', '20', 'Tools & Torches', '12', NULL, '', '3', 'Outdoor Fleece', '1', '23', '20', 'Auto accessoires', '3', '12'

How can I get the right subcategory back :)?

Upvotes: 0

Views: 33

Answers (1)

GMB
GMB

Reputation: 222402

If I followed you correctly, you seem to be missing a JOIN condition on the sub categories.

This :

LEFT JOIN  froets.sub_cat as PCAT 
    ON froets.producten.xncatncode = froets.PCAT.subcatid

Should be written as :

LEFT JOIN  froets.sub_cat as PCAT 
    ON froets.producten.xncatncode = froets.PCAT.subcatid 
    AND froets.producten.xngroupcode = froets.PCAT.catid

Upvotes: 1

Related Questions