[MySQL / MariaDB] Kom niet helemaal uit JOIN

[Gigazone]

Hallo,

Ik heb twee tabellen, namelijk tmp en products
Beide hebben een kolom voor Brand en voor EAN

in products heten deze kolommen EAN en brands
in tmp heten deze kolommen column02 en column08


Wat ik wil doen is 'column08' data uit 'tmp' toevoegen aan 'brands' in de tabel 'products, voor de rijen waar de value in EAN = value in column08


Ik heb een volgende statement:

code:

SELECT products.brand, tmp.column08 FROM products RIGHT JOIN tmp ON tmp.column02 = products.ean

Daaruit komt iets zoals:

code:

|--------------|--------------|
| brand         |column08  | 
|--------------|--------------|
|              | apple       |
|              | pear        |
|--------------|--------------|

Dat zal wel kloppen aangezien brand op het moment leeg is en ook het aantal matched met de data in de bron tabel. Nu wil ik dus daadwerkelijk de data gaan updaten, maar daar krijg ik het niet voor elkaar.

code:

INSERT INTO products (brand)
    SELECT products.brand, tmp.column08 FROM products RIGHT JOIN tmp ON tmp.column02 = products.ean

Maar dat mag niet : columncount doesn't match value in row 1. Nu verwacht ik dus dat de output van mijn select hiervan de oorzaak is. Als ik INSERT INTO products (brand, ean) doe, klopt het aantal kolommen wel maar ik wil maar 1 kolom wegschrijven en niet 2.

Als beginner zie ik waarschijnlijk iets heel voor de hand liggends over het hoofd, maar ik zie het even niet. Een pointer in de juiste richting zou in deze zeer welkom zijn !

[dev10]

De foutmelding die je krijgt, komt doordat je een insert statement maakt waarbij je aangeeft dat je gegevens gaat aanleveren voor 1 kolom, maar het resultaat uit je select statement bestaat uit twee kolommen.

Ik denk dat je geen right, maar een left join wil gebruiken om je data op te halen.

[CurlyMo]

Precies, je foutmelding heeft niks met een join statement te maken maar met het verkeerd gebruiken van een insert statement.

[Gigazone]

Okay, bedankt mijn select statement is dus verkeerd, ik heb deze aangepast
Langzaam maar zeker zal het gaan lukken!

code:

SELECT tmp.column08 FROM products left JOIN tmp ON tmp.column02 = products.ean

Als ik de select query uitvoer krijg ik 1 kolom, aangezien de selectt en insert nu het zelfde aantal kolommen bevat moet de foutmelding verdwijnen.

code:

INSERT INTO products (brand)
SELECT tmp.column08 FROM products left JOIN tmp ON tmp.column02 = products.ean

Deze query wordt uitgevoerd maar na een aantal seconden krijg ik de melding:

code:

#1062 - duplicate entry ' ' for key EAN

voor dat de query wordt uitgevoerd heb ik column02 naar EAN gekopieerd. EAN zou is een unieke waarde, en het heeft altijd een value. Ik heb ook nog in de tabellen gekeken maar zowel EAN als column02 hebben altijd een waarde. Had ik de kolom EAN in products niet als uniek moeten aanmerken? Of doe ik ergens anders iets fout?

[CurlyMo]

Gigazone schreef op donderdag 18 april 2019 @ 13:06:
Had ik de kolom EAN in products niet als uniek moeten aanmerken?

Dat kan je niet zo simpel stellen. Dat hangt van de specificaties af. Wil je wel of niet dubbele EAN's toestaan?

[dev10]

Had ik de kolom EAN in products niet als uniek moeten aanmerken? Of doe ik ergens anders iets fout?

Inderdaad. Je hebt een unique key staan, maar een lege string (of een string bestaand uit een spatie) wordt kan ook maar een keer voorkomen. Als je dubbele EAN's toe wilt staan, zul je de unique key moeten verwijderen.

[CurlyMo]

dev10 schreef op donderdag 18 april 2019 @ 13:09:
[...]


Inderdaad. Je hebt een unique key staan, maar een lege string (of een string bestaand uit een spatie) wordt kan ook maar een keer voorkomen. Als je dubbele EAN's toe wilt staan, zul je de unique key moeten verwijderen.

En/of in de selectie 'lege' EAN's filteren.

[Gigazone]

Ik wil sowieso geen dubbele EAN's toestaan. ieder product heeft een unieke EAN. Een EAN kan niet geldig zijn voor meerdere producten

met lege string bedoel je geen waarde toch? Ik snap dat een spatie (of iets anders) ook maar 1 maal mag voorkomen. Maar zowel tmp als products hebben alleen maar unieke waarden.

Kan ik ook "dubbele" waarden overslaan in mijn query zodat ik kan zien hoeveel het er zouden moeten zijn zodat ik naar deze op zoek kan gaan?

[ Voor 0% gewijzigd door Gigazone op 18-04-2019 13:13 . Reden: typo ]

[CurlyMo]

Gigazone schreef op donderdag 18 april 2019 @ 13:13:
Kan ik ook "dubbele" waarden overslaan in mijn query zodat ik kan zien hoeveel het er zouden moeten zijn zodat ik naar deze op zoek kan gaan?

Ja, dat kan. Ik ga het je alleen niet voorzeggen, want dan leer je niks

[ShitHappens]

Even kleine kanttekening:

Afhankelijk van de rest van de database, en wat voor applicatie er verder achter hangt, kán het voorkomen van een dubbele EAN valide zijn.

Bijvoorbeeld als je besluit dat één product meerdere leveranciers kan hebben (dus ook meerdere inkoopprijzen en bestelnummers), worden met deze met dezelfde EAN geleverd.
Exact hetzelfde product kan overigens ook met meerdere EAN's bekend zijn, of met een 8- en 13-nummerige.

Mochten dit 2 tabellen zijn puur om te leren hoe je met JOINs om moet gaan, heb ik niets gezegd Maar als je daadwerkelijk een database aan 't ontwerpen bent, is 't wel een puntje om rekening mee te houden.

[CurlyMo]

ShitHappens schreef op donderdag 18 april 2019 @ 13:29:
Maar als je daadwerkelijk een database aan 't ontwerpen bent, is 't wel een puntje om rekening mee te houden.

Net als dat in dat geval 'tmp' niet de beste tabelnaam is

[dev10]

CurlyMo schreef op donderdag 18 april 2019 @ 13:33:
[...]

Net als dat in dat geval 'tmp' niet de beste tabelnaam is

En column08 niet de beste kolomnaam.

[Gigazone]

Bedankt zover.

Ik heb 2 queries uitgevoerd

code:

SELECT column02
FROM tmp
GROUP BY column02
HAVING COUNT(*) > 1

en

code:

SELECT EAN
FROM products
GROUP BY EAN
HAVING COUNT(*) > 1

Beide queries geven 'zero' resultaat. Dus er zouden geen duplicates in moeten zitten.

Daarna heb ik de insert aangepast om de foutmelding te negeren.

code:

INSERT IGNORE INTO products (brand) 
SELECT tmp.column08 FROM products left JOIN tmp ON tmp.column02 = products.ean

Nu wordt er 1 regel toegevoegd aan 'products' met EAN ' ' (leeg)

Ik denk dat ik toch terug moet naar de tekentafel. column02 en EAN hebben geen dubbele waarden, de query geeft een juiste output. Op de een of andere manier lijkt er tijdens de INSERT iets niet goed te gaan, er alsof er niet wordt gekeken naar of column02 en EAN gelijk zijn.

[CurlyMo]

Of je voert je query meerdere keren uit op dezelfde tabel?

[Gigazone]

Volgens mij niet

[CurlyMo]

Doe je telling nog eens op het join resultaat i.p.v. de tabellen afzonderlijk.

[Osxy]

Is products (brand) wel leeg?

[Gigazone]

Osxy schreef op donderdag 18 april 2019 @ 19:36:
Is products (brand) wel leeg?

Op het ene toegevoegde veld (waar EAN = ' ') na wel ja. Maar stel nu dat het bijvoorbeeld een prijsveld zou zijn, dan zou ik bij een nieuwe join UPDATE moeten gebruiken in plaats van INSERT? En indien ja, zou dat dan ook kunnen gebruiken voor het initiele vullen van de kolom?

[AW_Bos]

En vanwaar je genummerde kolommen? Zoiets duidt vaak op een niet-genormaliseerde database.

[ Voor 45% gewijzigd door AW_Bos op 19-04-2019 00:19 ]

[Gigazone]

CurlyMo schreef op donderdag 18 april 2019 @ 19:26:
Doe je telling nog eens op het join resultaat i.p.v. de tabellen afzonderlijk.

code:

 Showing rows 0 - 24 (15001 total, Query took 0.1241 seconds.)

Ik had er 15000 toegevoegd, de join query had er ook een toegevoegd, dus totaal 15001 (niet unieke waardes)

[Gigazone]

AW_Bos schreef op vrijdag 19 april 2019 @ 00:18:
En vanwaar je genummerde kolommen? Zoiets duidt vaak op een niet-genormaliseerde database.

tmp is een tijdelijke database waar de naam van de kolom niet van belang is, de ene keer is column02 EAN, de andere keer is column02 iets anders, vandaar geen vaste naam.

[Gigazone]

ShitHappens schreef op donderdag 18 april 2019 @ 13:29:
Even kleine kanttekening:

Afhankelijk van de rest van de database, en wat voor applicatie er verder achter hangt, kán het voorkomen van een dubbele EAN valide zijn.

Bijvoorbeeld als je besluit dat één product meerdere leveranciers kan hebben (dus ook meerdere inkoopprijzen en bestelnummers), worden met deze met dezelfde EAN geleverd.
Exact hetzelfde product kan overigens ook met meerdere EAN's bekend zijn, of met een 8- en 13-nummerige.

Mochten dit 2 tabellen zijn puur om te leren hoe je met JOINs om moet gaan, heb ik niets gezegd Maar als je daadwerkelijk een database aan 't ontwerpen bent, is 't wel een puntje om rekening mee te houden.

Op het moment is het puur om te leren en een proof of concept te creeren.

Bedankt voor de tip voor meerdere rijen per EAN. Ik zat zelf te denken om per leverancier een extra kolom te maken. Maar uw suggestie zou wellicht wel een betere optie zijn. Helaas kan ik niet zovceel informatie vinden over hoe je een db opzet met prijzen en producten van meerdere leveranciers. Ik zit de denken aan een EAV structuur waarbij de attributen in een andere tabel komen. Ik ik heb gelezen dat bijvoorbeeld tweakers zelf ook de catagorieen in een andere tabel heeft.


Maar zoals ik al aangaf is het nu even een proof of concept. daarna kan ik me verdiepen in wat de beste manier is op de database op te zetten.

[Gigazone]

Ik ben inmiddels wat verder.

Ik heb de unieke key van de kolom EAN in de table 'products' gehaald om te kijken wat er nu daadwerkelijk gebeurd.

begin status 15000 (rijen) ean in products

code:

INSERT INTO products (brand) 
SELECT tmp.column08 FROM products left JOIN tmp ON tmp.column02 = products.EAN

En tadaa... 30000 rijen in de tabel producten.
Alle rijen met EAN hebben geen brand, en alle rijen met brand hebben Geen EAN.

Maar wat ik wil is als de value in colomn08 uit tmp == EAN in products, voeg dan brands toe aan de rij met deze EAN.


Ik denk wat er gebeurt is: als de value in colomn08 uit tmp == EAN in products, voeg dan een nieuwe rij met het veld brands toe aan de tabel products.

Dus ik denk dat ik geen insert moet gebruiken maar een update.


----
update

code:

UPDATE products, tmp
SET products.brand = tmp.column08 
WHERE tmp.column02 = products.EAN

Bovenstaande blijkt te werken. ik kan het nog ombouwen met een JOIN statement.

Bedankt voor de feedback en de suggesties.!

[ Voor 14% gewijzigd door Gigazone op 19-04-2019 13:06 ]

[Gigazone]

ShitHappens schreef op donderdag 18 april 2019 @ 13:29:

Bijvoorbeeld als je besluit dat één product meerdere leveranciers kan hebben (dus ook meerdere inkoopprijzen en bestelnummers), worden met deze met dezelfde EAN geleverd.
Exact hetzelfde product kan overigens ook met meerdere EAN's bekend zijn, of met een 8- en 13-nummerige.

Stel nu dat je meerdere prijzen zien bij een EAN (dus na het inlezen van meerdere feeds)

Zou het dan sneller en praktischer zijn om:

1) 1 table met meerdere rijen per EAN, dus voor ieder artikel een losse rij per leverancier (lang)
2) 1 table met 1 rij per artikel met meerdere kollommen met de prijs per leverancier (breed)
3) 1 table met 1 rij per artikel met basic info. de prijzen (en attributen) in een andere tabel (EAV)

wat is de meest toekomstbestendige oplossing, voor 1mln+ prijzen bijvoorbeeld

[ShitHappens]

Nu ligt het heel erg aan wat voor database 't nu precies is, en wat het uiteindelijk moet kunnen gaan doen.

Ik zou zelf heel erg geneigd zijn om, als ik het zelf zou moeten ontwerpen, het volgende te doen:

Tabel Product
- ProductId
- Naam
- Andere data die echt specifiek zijn voor 't product

Tabel ProductEAN
- ProductId
- EAN

Tabel ProductInkoop
- ProductId
- Leverancier
- Inkoopprijs
- Eventueel geldigheidsperiode van de inkoopprijjs, als je 't verloop daarvan bij wilt houden

Voor de verkoopprijs, kun je dat in een simpele vorm op Product bijhouden (enkel laatste verkoopprijs), maar ProductVerkoop met ProductId, prijs (en verdere data.... BTW tarief? Geldigheidsperiode?)

[Osxy]

Gigazone schreef op vrijdag 26 april 2019 @ 12:53:
[...]


Stel nu dat je meerdere prijzen zien bij een EAN (dus na het inlezen van meerdere feeds)

Zou het dan sneller en praktischer zijn om:

1) 1 table met meerdere rijen per EAN, dus voor ieder artikel een losse rij per leverancier (lang)
2) 1 table met 1 rij per artikel met meerdere kollommen met de prijs per leverancier (breed)
3) 1 table met 1 rij per artikel met basic info. de prijzen (en attributen) in een andere tabel (EAV)

wat is de meest toekomstbestendige oplossing, voor 1mln+ prijzen bijvoorbeeld

2 zou ik nooit doen, zorgt voor veel loze kolommen en moeilijke onderhoudbaarheid.

1 en 3 kunnen beide en hangen af van je data, is moeilijk iets over te zeggen.


Ik zou naar 3 neigen, alle uniforme data in de main tabel en de leverancier specifieke details in aparte tabel. Maar kan me ook voorstellen dat je prijs historie wilt bijhouden. Dan zou je hier nog een aparte tabel voor kunnen maken.

Zoiets: https://www.db-fiddle.com/f/tCJskPQ3KiR7n3nVumA7us/1

[Gigazone]

Bedankt voor de suggestie!


Ik neig zelf ook het meeste naar 3. Ik heb gelezen dat Tweakers zelf ook iets dergelijks gebruikt waarbij ze categorien lostrekken. https://gathering.tweakers.net/forum/list_messages/1715431

ShitHappens schreef op vrijdag 26 april 2019 @ 13:11:


Tabel Product
- ProductId
- Naam
- Andere data die echt specifiek zijn voor 't product

Dan bedoel je ook kleur, vorm, afwerking, gewicht etc dus echt de attributen, toch?

Dus dan heb je


Tabel ProductEAN (MAIN)
Tabel Product (ATTRIBUTE informatie)
Tabel ProductInkoop (Prijs informatie)


De categories zijn vaak een interpretatie van de leverancier. Bij de 1 heet het het toetsenbord, bij de ander keyboard en een derde heft heet over input devices.

Zou je dan de categorie inderdaad ook los moeten trekken naar een eigen tabel en deze koppelen aan een eigen format? Bijvoorbeeld

TabelProductCategory
toetsenbord = KB
keyboard = KB
input devices = KB
etc


en dan intern "KB" als categorie gebruiken?

[Gigazone]

ShitHappens schreef op vrijdag 26 april 2019 @ 13:11:
Voor de verkoopprijs, kun je dat in een simpele vorm op Product bijhouden (enkel laatste verkoopprijs), maar ProductVerkoop met ProductId, prijs (en verdere data.... BTW tarief? Geldigheidsperiode?)

Sorry, ik heb nog een additionele vraag ter lering (en vermaak). Stel nu dat ik net als bij tweakers en hardware info een grafiekje wil met historische prijzen.

Hoe sla je uberhaupt zoiets op in een database als je miljoenen producten hebt? Want ik neem aan dat je zoiets op slaat per product, per leverancier en per dag. En dan nog een max/min/gemiddeld. Krijg je dan niet een enorme bloated db? Wellicht dat je het kunt reduceren tot alleen gemiddelden per product per dag. Maar dan nog is dat een hele berg data die je iedere dag moet opslaan en bewaren.