[MySQL] Data ophalen meerdere tabellen met dubbele data

[Yagermeister]

Mijn vraag

Wij zijn bezig om in een magento database diverse queries aan het maken om zo op een makkelijke manier data op te halen voor een eigen reportage.

In totaal bevindt zich de data in 4 tabellen namelijk: sales_flat_order, purchase_product_serial, purchase_order en purchase_supplier

Op zich de data ophalen is geen probleem echter is bij de tabel purchase_product_serial een probleem dat het serienummer op 2 regels staat, namelijk voor de inkoop order en voor de verkoop order.

Wat ik al gevonden of geprobeerd heb

Ik ben zover dat ik onderstaande query werkend heb en daarmee krijg ik een groot gedeelte van de data echter is alles dus op 2 regels en heb ik geen leverancier naam erbij staan. Zodra ik deze namelijk in de query erbij voeg krijg ik geen data meer terug.

code:

SELECT
sales_flat_order.entity_id,
sales_flat_order.increment_id AS `Order ID`,
sales_flat_order.created_at AS Besteldatum,
sales_flat_order.`status`,
purchase_order.po_sup_num,
purchase_product_serial.pps_serial,
purchase_product_serial.pps_salesorder_id,
purchase_product_serial.pps_purchaseorder_id
FROM
purchase_product_serial
LEFT JOIN sales_flat_order ON sales_flat_order.entity_id = purchase_product_serial.pps_salesorder_id
LEFT JOIN purchase_order ON purchase_product_serial.pps_purchaseorder_id = purchase_order.po_num
GROUP BY
purchase_product_serial.pps_serial

Weet iemand misschien hoe ik de code als zodanig moet/kan aanpassen zodat ik en de leverancier te zien krijg en de dubbele regels niet meer heb?

[IWriteCode]

Ik denk dat je moet joinen op het veld pps_serial, aangezien deze de verbindende factor is... Of 2 subselecties maken... en die dan weer joinen op het pps_serial veld...

Heb je een voorbeeld van de (relevante) data van de 4 tabellen en wat je er uit wil hebben?

[ Voor 21% gewijzigd door IWriteCode op 27-12-2016 13:32 ]

[Montaner]

Cross join gebruiken?

http://www.w3resource.com...ysql/mysql-cross-join.php

Edit.. oh, niet geweldig gelezen. Dan ga je dus alle records dubbel krijgen (zoals je nu eigenlijk ook al hebt).

[ Voor 36% gewijzigd door Montaner op 27-12-2016 13:35 ]

[IWriteCode]

Heb even een snel voorbeeldtabelletje gemaakt voor het combineren van de serienummers:

a	b	c
a	b	null
a	null	c
a	e	null
a	null	f

code:

select tabel_b.a,tabel_b.b,tabel_c.c from
(select a,b from test where not b is null) as tabel_b, 
(select a,c from test where not c is null) as tabel_c
where tabel_b.a=tabel_c.a

a	b	c
a	b	c
a	e	f

Dan kan je die vervolgens gebruiken om de rest mee te joinen

Deze voor jouw situatie?

code:

SELECT
sales_flat_order.entity_id,
sales_flat_order.increment_id AS `Order ID`,
sales_flat_order.created_at AS Besteldatum,
sales_flat_order.`status`,
purchase_order.po_sup_num,
pps.*
FROM
(select so.pps_serial, so.pps_salesorder_id, po.pps_purchaseorder_id from
(select pps_serial,pps_salesorder_id from purchase_product_serial where not pps_salesorder_id is null) as so, 
(select pps_serial,pps_purchaseorder_id from purchase_product_serial where not pps_purchaseorder_id is null) as po
where so.pps_serial=po.pps_serial) as pps
LEFT JOIN sales_flat_order ON pps.pps_salesorder_id = sales_flat_order.entity_id
LEFT JOIN purchase_order ON pps.pps_purchaseorder_id = purchase_order.po_num

[ Voor 37% gewijzigd door IWriteCode op 27-12-2016 15:41 ]

[Yagermeister]

IWriteCode schreef op dinsdag 27 december 2016 @ 13:44:
Heb even een snel voorbeeldtabelletje gemaakt voor het combineren van de serienummers:

a	b	c
a	b	null
a	null	c
a	e	null
a	null	f

code:

select tabel_b.a,tabel_b.b,tabel_c.c from
(select a,b from test where not b is null) as tabel_b, 
(select a,c from test where not c is null) as tabel_c
where tabel_b.a=tabel_c.a

a	b	c
a	b	c
a	e	f

Dan kan je die vervolgens gebruiken om de rest mee te joinen

Deze voor jouw situatie?

code:

SELECT
sales_flat_order.entity_id,
sales_flat_order.increment_id AS `Order ID`,
sales_flat_order.created_at AS Besteldatum,
sales_flat_order.`status`,
purchase_order.po_sup_num,
pps.*
FROM
(select so.pps_serial, so.pps_salesorder_id, po.pps_purchaseorder_id from
(select pps_serial,pps_salesorder_id from purchase_product_serial where not pps_salesorder_id is null) as so, 
(select pps_serial,pps_purchaseorder_id from purchase_product_serial where not pps_purchaseorder_id is null) as po
where so.pps_serial=po.pps_serial) as pps
LEFT JOIN sales_flat_order ON pps.pps_salesorder_id = sales_flat_order.entity_id
LEFT JOIN purchase_order ON pps.po_num = purchase_product_serial.pps_purchaseorder_id

Ik heb jouw code even geprobeerd maar ik krijg onderstaande error en ik zie niet zo snel waar het probleem ligt aangezien alle benamingen anders zijn.

code:

[SQL]SELECT
sales_flat_order.entity_id,
sales_flat_order.increment_id AS `Order ID`,
sales_flat_order.created_at AS Besteldatum,
sales_flat_order.`status`,
purchase_order.po_sup_num,
pps.*
FROM
(select so.pps_serial, so.pps_salesorder_id, po.pps_purchaseorder_id from
(select pps_serial,pps_salesorder_id from purchase_product_serial where not pps_salesorder_id is null) as so, 
(select pps_serial,pps_purchaseorder_id from purchase_product_serial where not pps_purchaseorder_id is null) as po
where so.pps_serial=po.pps_serial) AS pps
LEFT JOIN sales_flat_order ON pps.pps_salesorder_id= sales_flat_order.entity_id
LEFT JOIN purchase_order ON pps.po_num= purchase_product_serial.pps_purchaseorder_id

[Err] 1054 - Unknown column 'pps.po_num' in 'on clause'

Ik zie bijv so.pps_serial, so.pps_salesorder_id, po.pps_purchaseorder_id echter heb ik geen sales_flat_order.pps_serial?

Misschien is het even handiger als ik een foto plaats van de tabellen met de correcte relaties ertussen.




[Toevoeging]
Ik ben even naar de tabel aan het kijken met die a b c om te zien of ik hem toch kan snappen. Als ik het goed zie is A het serienummer aangezien dat in elke rij staat. B is dan de inkoop (even om het makkelijk te houden) en C is dan de verkoop.

Nu moet ik dus die 2 temp tabellen vullen met de 2 losse queries en daarna pas joinen met de andere? Klopt dat zo ongeveer als ik het goed begrijp?

[ Voor 10% gewijzigd door Yagermeister op 27-12-2016 15:00 ]

[IWriteCode]

Aah, heb 2 kolommen omgewisseld... monument...

Heb het aangepast in mn oorspronkelijke post. Had 2 velden omgewisseld =)

code:

select so.pps_serial, so.pps_salesorder_id, po.pps_purchaseorder_id from
(select pps_serial,pps_salesorder_id from purchase_product_serial where not pps_salesorder_id is null) as so, 
(select pps_serial,pps_purchaseorder_id from purchase_product_serial where not pps_purchaseorder_id is null) as po
where so.pps_serial=po.pps_serial

^^ dit zou als het goed is beide serienummers gecombineerd moeten zijn...

[ Voor 108% gewijzigd door IWriteCode op 27-12-2016 15:44 ]

[Yagermeister]

IWriteCode schreef op dinsdag 27 december 2016 @ 15:39:
Aah, heb 2 kolommen omgewisseld... monument...

Heb het aangepast in mn oorspronkelijke post. Had 2 velden omgewisseld =)

code:

select so.pps_serial, so.pps_salesorder_id, po.pps_purchaseorder_id from
(select pps_serial,pps_salesorder_id from purchase_product_serial where not pps_salesorder_id is null) as so, 
(select pps_serial,pps_purchaseorder_id from purchase_product_serial where not pps_purchaseorder_id is null) as po
where so.pps_serial=po.pps_serial

^^ dit zou als het goed is beide serienummers gecombineerd moeten zijn...

Ik ga dit morgen even proberen zodra ik tijd heb. Alvast bedankt voor de hulp!

[jbdeiman]

Yagermeister schreef op dinsdag 27 december 2016 @ 13:14:
Mijn vraag

Wij zijn bezig om in een magento database diverse queries aan het maken om zo op een makkelijke manier data op te halen voor een eigen reportage.

In totaal bevindt zich de data in 4 tabellen namelijk: sales_flat_order, purchase_product_serial, purchase_order en purchase_supplier

Op zich de data ophalen is geen probleem echter is bij de tabel purchase_product_serial een probleem dat het serienummer op 2 regels staat, namelijk voor de inkoop order en voor de verkoop order.

Wat ik al gevonden of geprobeerd heb

Ik ben zover dat ik onderstaande query werkend heb en daarmee krijg ik een groot gedeelte van de data echter is alles dus op 2 regels en heb ik geen leverancier naam erbij staan. Zodra ik deze namelijk in de query erbij voeg krijg ik geen data meer terug.

code:

SELECT
sales_flat_order.entity_id,
sales_flat_order.increment_id AS `Order ID`,
sales_flat_order.created_at AS Besteldatum,
sales_flat_order.`status`,
purchase_order.po_sup_num,
purchase_product_serial.pps_serial,
purchase_product_serial.pps_salesorder_id,
purchase_product_serial.pps_purchaseorder_id
FROM
purchase_product_serial
LEFT JOIN sales_flat_order ON sales_flat_order.entity_id = purchase_product_serial.pps_salesorder_id
LEFT JOIN purchase_order ON purchase_product_serial.pps_purchaseorder_id = purchase_order.po_num
GROUP BY
purchase_product_serial.pps_serial

[afbeelding]

Weet iemand misschien hoe ik de code als zodanig moet/kan aanpassen zodat ik en de leverancier te zien krijg en de dubbele regels niet meer heb?

Wil je alleen de volledige regels uit je resultaat afbeelding hebben? Zo ja, verander de LEFT JOINS eens in INNER JOINS dan.
Een LEFT JOIN probeert te zoeken naar resultaten die er zijn, zijn die er niet, dan toont die ze ook niet, en krijg je dus al die NULL waarden terug.

Een INNER JOIN geeft alleen resultaat terug wanneer in beide tabellen een gekoppeld resultaat is gegeven.
Ik denk iig dat je dat bedoeld met je dubbele regels.

Zoiets dus?

code:

SELECT
sales_flat_order.entity_id,
sales_flat_order.increment_id AS `Order ID`,
sales_flat_order.created_at AS Besteldatum,
sales_flat_order.`status`,
purchase_order.po_sup_num,
purchase_product_serial.pps_serial,
purchase_product_serial.pps_salesorder_id,
purchase_product_serial.pps_purchaseorder_id
FROM
purchase_product_serial
INNER JOIN sales_flat_order ON sales_flat_order.entity_id = purchase_product_serial.pps_salesorder_id
INNER JOIN purchase_order ON purchase_product_serial.pps_purchaseorder_id = purchase_order.po_num
INNER JOIN purchase_supplier ON purchase_order.po_sup_num = purchase_supplier.sup_id
GROUP BY
purchase_product_serial.pps_serial

[ Voor 13% gewijzigd door jbdeiman op 27-12-2016 17:03 ]

[Dido]

jbdeiman schreef op dinsdag 27 december 2016 @ 17:01:
Een INNER JOIN geeft alleen resultaat terug wanneer in beide tabellen een gekoppeld resultaat is gegeven.
Ik denk iig dat je dat bedoeld met je dubbele regels.

Denk dat ie dan weinig terug gaat krijgen. Het probleem is simpelweg dat hij uit dezelfde tabel gegevens van twee verschillende records wil zien, en dat betekent simpelweg dat je die tabel twee keer moet joinen, 1 keer om de info van regel 1 op te halen, en 1 keer voor de info van regel 2.

[Yagermeister]

jbdeiman schreef op dinsdag 27 december 2016 @ 17:01:
[...]

Wil je alleen de volledige regels uit je resultaat afbeelding hebben? Zo ja, verander de LEFT JOINS eens in INNER JOINS dan.
Een LEFT JOIN probeert te zoeken naar resultaten die er zijn, zijn die er niet, dan toont die ze ook niet, en krijg je dus al die NULL waarden terug.

Een INNER JOIN geeft alleen resultaat terug wanneer in beide tabellen een gekoppeld resultaat is gegeven.
Ik denk iig dat je dat bedoeld met je dubbele regels.

Zoiets dus?

code:

SELECT
sales_flat_order.entity_id,
sales_flat_order.increment_id AS `Order ID`,
sales_flat_order.created_at AS Besteldatum,
sales_flat_order.`status`,
purchase_order.po_sup_num,
purchase_product_serial.pps_serial,
purchase_product_serial.pps_salesorder_id,
purchase_product_serial.pps_purchaseorder_id
FROM
purchase_product_serial
INNER JOIN sales_flat_order ON sales_flat_order.entity_id = purchase_product_serial.pps_salesorder_id
INNER JOIN purchase_order ON purchase_product_serial.pps_purchaseorder_id = purchase_order.po_num
INNER JOIN purchase_supplier ON purchase_order.po_sup_num = purchase_supplier.sup_id
GROUP BY
purchase_product_serial.pps_serial

Het is zo dat er soms regels zijn waar alleen een PO aan hangt omdat het product bijv nog niet verkocht is. Bij deze heb ik dus een gedeelte lege tekst. Deze wil ik echter filteren door de status complete te gebruiken. Hiermee moet ik dan het ordernummer krijgen met daarbij het serienummer en van welke leverancier het is.

Met mijn eigen query zoals ik hem had kreeg ik dus al een gedeelte echter was dit op 2 regels en lukte het niet om hier de leveranciersnaam aan te koppelen. Met de code van IWriteCode is het me gelukt om een gedeelte terug te krijgen echter merkte ik dat de meeste regels allemaal een 10x dubbel waren. Mogelijk heb ik me ergens vertypt ofzo waardoor dit niet helemaal correct was. Ik heb hier echter niet veel tijd gehad om heen te kijken. Dit was wel in de richting wat ik nodig heb.

Zoals Dido het zegt is inderdaad wat er zou moeten gebeuren echter weet ik niet precies hoe ik dit moet vertalen.

[IWriteCode]

je kan er een SELECT DISTINCT van maken, dan krijg je geen dubbele identieke regels.

[EvilWhiteDragon]

Als ik het probleem goed begrijp heb je zowel in- als verkooporders in dezelfde tabel(len) staan, correct? Is er een manier om onderscheid te maken tussen in- en verkoop? Zo ja dan krijg is iets als:

code:

SELECT  DISTINCT * -- Niet doen ;-)
FROM purchase_order AS po 
INNER JOIN purchase_product_serial AS inkoop -- Neem aan dat alles wat je verkoopt ooit ingekocht is
ON inkoop.pps_purchaseorder_id = po.po_num
AND <clausule waardoor je weet dat het inkoop is>
LEFT OUTER JOIN purchase_product_serial AS verkoop -- LEFT OUTER JOIN omdat je het mogelijk nog niet verkocht hebt.
ON inkoop.pps_serial = verkoop.pps_serial
AND <clausule waardoor je weet dat het verkoop is>
LEFT OUTER JOIN sales_flat_order AS sfo
ON sfo.entity_id = inkoop.pps_salesorder_id

[Yagermeister]

EvilWhiteDragon schreef op dinsdag 27 december 2016 @ 22:34:
Als ik het probleem goed begrijp heb je zowel in- als verkooporders in dezelfde tabel(len) staan, correct? Is er een manier om onderscheid te maken tussen in- en verkoop? Zo ja dan krijg is iets als:

code:

SELECT  DISTINCT * -- Niet doen ;-)
FROM purchase_order AS po 
INNER JOIN purchase_product_serial AS inkoop -- Neem aan dat alles wat je verkoopt ooit ingekocht is
ON inkoop.pps_purchaseorder_id = po.po_num
AND <clausule waardoor je weet dat het inkoop is>
LEFT OUTER JOIN purchase_product_serial AS verkoop -- LEFT OUTER JOIN omdat je het mogelijk nog niet verkocht hebt.
ON inkoop.pps_serial = verkoop.pps_serial
AND <clausule waardoor je weet dat het verkoop is>
LEFT OUTER JOIN sales_flat_order AS sfo
ON sfo.entity_id = inkoop.pps_salesorder_id

Er is inderdaad onderscheid tussen beide soorten orders namelijk in de purchase_product_serial zijn er 2 kolomen waarvan 1 pps_salesorder_id heet en de ander pps_purchaseorder_id. Ik zal vandaag eens proberen te knutselen met de code van hierboven om te zien of ik het werkend krijg.


[Edit]

Ik heb zojuist bovenstaande code aangepast naar onderstaande:

code:

SELECT DISTINCT
sfo.entity_id,
sfo.increment_id,
sfo.created_at,
sfo.`status`,
po.po_sup_num,
inkoop.pps_serial,
inkoop.pps_salesorder_id,
inkoop.pps_purchaseorder_id
FROM
purchase_order AS po
INNER JOIN purchase_product_serial AS inkoop ON inkoop.pps_purchaseorder_id = po.po_num AND inkoop.pps_purchaseorder_id IS NOT NULL
LEFT OUTER JOIN purchase_product_serial AS verkoop ON inkoop.pps_serial = verkoop.pps_serial AND inkoop.pps_salesorder_id IS NOT NULL
LEFT OUTER JOIN sales_flat_order AS sfo ON sfo.entity_id = inkoop.pps_salesorder_id

Nu krijg ik als result onderstaande.



Ik zie wel geen dubbele serienummers dus dat stukje is in ieder geval goed. Daarbij zie ik van 2 stuks dat het wel is samengevoegd echter is dat een andere "Store" wat eigenlijk niet relevant is. Hier maak ik echter geen onderscheid in als die meegenomen wordt.

Hoe kan het dat wel van 1 shop de data samengevoegd wordt maar niet van de andere?

[ Voor 33% gewijzigd door Yagermeister op 29-12-2016 12:25 . Reden: Toevoeging ]

[Yagermeister]

Even bumpen.

[Dido]

Ten eerste, als het goed is heb je geen DISTINCT nodig. Dit is meestal SQL codesmell.

Ten tweede, het is onduidelijk waarom je nieuwe resultaten afwijken van wat je verwacht. Ten opzichte van je eerste screenshot met resultaten gaat het nu namelijk om andere data. Als er purchase orders zijn zonder sales order of vice versa, dan ziet je nieuwe resultaat er op zich niet verkeerd uit.
Ik vraag me alleen af wat je aan het doen met met die IS NOT NULL condities in combinatie met outer joins. Outer joins zijn er juist om NULL-results wel terug te krijgen.

Eens kijken of we stap voor stap tot een resultaat kunnen komen.

De "hoofdtabel" is purchase_product_serial. Als ik het goed begrijp zijn er steeds 1 of 2 records met hetzelfde pps_serial, waarin of pps_salesorder_id of pps_purchase_order_id gevuld is.

Laten we eens beginnen met alle bij elkaar horende sales- en purchase order id's bij elkaar te rapen. Dat gaan we doen met een FULL OUTER JOIN, ervan uitgaande dat er purchases zonder sales, maar ook sales zonder purchase kunnen zijn:

code:

SELECT COALESCE(pps_s.pps_serial, pps_p.pps_serial) as serial
     , pps_s.pps_salesorder_id as salesorder_id
     , pps_p.pps_purchaseorder_id as purchaseorder_id
           FROM purchase_product_serial as pps_s
FULL OUTER JOIN purchase_product_serial as pps_p
             ON pps_s.pps_serial = pps_p.pps_serial

Wat we hebben is dus PPS gejoined met zichzelf op pps_serial. de COALESCE zorgt ervoor dat we die serial altijd zien, ook al kan (in ieder geval in theorie) een van de twee serials leeg zijn.

Dit is de basis voor je query. Als je hier inderdaad alle PPS_serials in teugziet met de bijbehorende purchase / sales order id's, dan kun je verder gaan.

Om de sales-order gegevens erbij te halen vervangen we purchase_product_serial as pps_s door purchase_purchase_serial as pps_s met een (inner) JOIN op sales_flat_order:

code:

SELECT COALESCE(pps_s.pps_serial, pps_p.pps_serial) as serial
     , pps_s.pps_salesorder_id as salesorder_id
     , sfo.status as status
     , pps_p.pps_purchaseorder_id as purchaseorder_id
           FROM (     purchase_product_serial as pps_s 
                 JOIN sales_flat_order as sfo 
                   ON sfo.entity_id = pps_s.salesorder_id)
FULL OUTER JOIN purchase_product_serial as pps_p
             ON pps_s.pps_serial = pps_p.pps_serial

En voor de purchase orders doen we vervolgens hetzelfde met de pps_p, we joinen purchase_order en purchase_suplier tabellen:

code:

SELECT COALESCE(pps_s.pps_serial, pps_p.pps_serial) as serial
     , pps_s.pps_salesorder_id as salesorder_id
     , sfo.status as status
     , pps_p.pps_purchaseorder_id as purchaseorder_id
     , po.po_sup_num
     , ps.sup_name
           FROM (     purchase_product_serial as pps_s 
                 JOIN sales_flat_order as sfo 
                   ON sfo.entity_id = pps_s.salesorder_id)
FULL OUTER JOIN (     purchase_product_serial as pps_p
                 JOIN purchase_order as po 
                   ON po.po_num = pps_p.purchaseorder_id
                 JOIN purchase_supplier ps 
                   ON ps.sup_id = po.sup_num)
             ON pps_s.pps_serial = pps_p.pps_serial

Uiteraard kun je in de SELECT clause de kolommen aangeven die je daadwerkelijk wilt zien, ik heb even een paar voorbeelden neergezet.

Tot slot een paar algemene opmerkingen:

1) Als je gewoon de purchase en sales order id's in hetzelfde record zou hebben bijgehouden (ze horen immers toch bij elkaar!) dan was dit probleem nooit ontstaan.

2) De naamgeving van je kolommen is inconsistent. De ene keer heet iets id, hetzelfde veld als foreign keey heet opeens num. dat is verwarrend.

3) Het prefixen van de kolomnamen met een tabelnaam-afkorting heeft weinig nut en zorgt alleen voor extra tikwerk. Een id van een entiteit mag gewoon id heten, en als je die als foreign key gebruikt, gebruik dan een consistente afkorting, gevolgd door _id.

Ter illustratie zal ik de laatste query even herschrijven alsof er een wat gangbaardere en consistentere naamgeving was gebruikt in je database. Na wat mooi maken ziet dit er toch overzichtelijker uit, lijkt me:

code:

SELECT COALESCE(pps_s.serial, pps_p.serial) AS serial
     , pps_s.so_id
     , sfo.status 
     , pps_p.po_id
     , po.id
     , ps.name
           FROM (     purchase_product_serial pps_s 
                 JOIN sales_order             so    ON so.id = pps_s.so_id)
FULL OUTER JOIN (     purchase_product_serial pps_p
                 JOIN purchase_order          po    ON po.id = pps_p.po_id
                 JOIN purchase_supplier       ps    ON ps.id = po.ps_id)
             ON pps_s.serial = pps_p.serial

[Yagermeister]

Dido schreef op zaterdag 31 december 2016 @ 17:44:
Ten eerste, als het goed is heb je geen DISTINCT nodig. Dit is meestal SQL codesmell.

Ten tweede, het is onduidelijk waarom je nieuwe resultaten afwijken van wat je verwacht. Ten opzichte van je eerste screenshot met resultaten gaat het nu namelijk om andere data. Als er purchase orders zijn zonder sales order of vice versa, dan ziet je nieuwe resultaat er op zich niet verkeerd uit.
Ik vraag me alleen af wat je aan het doen met met die IS NOT NULL condities in combinatie met outer joins. Outer joins zijn er juist om NULL-results wel terug te krijgen.

Eens kijken of we stap voor stap tot een resultaat kunnen komen.

De "hoofdtabel" is purchase_product_serial. Als ik het goed begrijp zijn er steeds 1 of 2 records met hetzelfde pps_serial, waarin of pps_salesorder_id of pps_purchase_order_id gevuld is.

Laten we eens beginnen met alle bij elkaar horende sales- en purchase order id's bij elkaar te rapen. Dat gaan we doen met een FULL OUTER JOIN, ervan uitgaande dat er purchases zonder sales, maar ook sales zonder purchase kunnen zijn:

code:

SELECT COALESCE(pps_s.pps_serial, pps_p.pps_serial) as serial
     , pps_s.pps_salesorder_id as salesorder_id
     , pps_p.pps_purchaseorder_id as purchaseorder_id
           FROM purchase_product_serial as pps_s
FULL OUTER JOIN purchase_product_serial as pps_p
             ON pps_s.pps_serial = pps_p.pps_serial

Wat we hebben is dus PPS gejoined met zichzelf op pps_serial. de COALESCE zorgt ervoor dat we die serial altijd zien, ook al kan (in ieder geval in theorie) een van de twee serials leeg zijn.

Dit is de basis voor je query. Als je hier inderdaad alle PPS_serials in teugziet met de bijbehorende purchase / sales order id's, dan kun je verder gaan.

Om de sales-order gegevens erbij te halen vervangen we purchase_product_serial as pps_s door purchase_purchase_serial as pps_s met een (inner) JOIN op sales_flat_order:

code:

SELECT COALESCE(pps_s.pps_serial, pps_p.pps_serial) as serial
     , pps_s.pps_salesorder_id as salesorder_id
     , sfo.status as status
     , pps_p.pps_purchaseorder_id as purchaseorder_id
           FROM (     purchase_product_serial as pps_s 
                 JOIN sales_flat_order as sfo 
                   ON sfo.entity_id = pps_s.salesorder_id)
FULL OUTER JOIN purchase_product_serial as pps_p
             ON pps_s.pps_serial = pps_p.pps_serial

En voor de purchase orders doen we vervolgens hetzelfde met de pps_p, we joinen purchase_order en purchase_suplier tabellen:

code:

SELECT COALESCE(pps_s.pps_serial, pps_p.pps_serial) as serial
     , pps_s.pps_salesorder_id as salesorder_id
     , sfo.status as status
     , pps_p.pps_purchaseorder_id as purchaseorder_id
     , po.po_sup_num
     , ps.sup_name
           FROM (     purchase_product_serial as pps_s 
                 JOIN sales_flat_order as sfo 
                   ON sfo.entity_id = pps_s.salesorder_id)
FULL OUTER JOIN (     purchase_product_serial as pps_p
                 JOIN purchase_order as po 
                   ON po.po_num = pps_p.purchaseorder_id
                 JOIN purchase_supplier ps 
                   ON ps.sup_id = po.sup_num)
             ON pps_s.pps_serial = pps_p.pps_serial

Uiteraard kun je in de SELECT clause de kolommen aangeven die je daadwerkelijk wilt zien, ik heb even een paar voorbeelden neergezet.

Tot slot een paar algemene opmerkingen:

1) Als je gewoon de purchase en sales order id's in hetzelfde record zou hebben bijgehouden (ze horen immers toch bij elkaar!) dan was dit probleem nooit ontstaan.

2) De naamgeving van je kolommen is inconsistent. De ene keer heet iets id, hetzelfde veld als foreign keey heet opeens num. dat is verwarrend.

3) Het prefixen van de kolomnamen met een tabelnaam-afkorting heeft weinig nut en zorgt alleen voor extra tikwerk. Een id van een entiteit mag gewoon id heten, en als je die als foreign key gebruikt, gebruik dan een consistente afkorting, gevolgd door _id.

Ter illustratie zal ik de laatste query even herschrijven alsof er een wat gangbaardere en consistentere naamgeving was gebruikt in je database. Na wat mooi maken ziet dit er toch overzichtelijker uit, lijkt me:

code:

SELECT COALESCE(pps_s.serial, pps_p.serial) AS serial
     , pps_s.so_id
     , sfo.status 
     , pps_p.po_id
     , po.id
     , ps.name
           FROM (     purchase_product_serial pps_s 
                 JOIN sales_order             so    ON so.id = pps_s.so_id)
FULL OUTER JOIN (     purchase_product_serial pps_p
                 JOIN purchase_order          po    ON po.id = pps_p.po_id
                 JOIN purchase_supplier       ps    ON ps.id = po.ps_id)
             ON pps_s.serial = pps_p.serial

Bedankt voor dit antwoord. Ik ga even in stukjes hierop reageren om dit overzichtelijk te houden.

code:

Ten tweede, het is onduidelijk waarom je nieuwe resultaten afwijken van wat je verwacht. Ten opzichte van je eerste screenshot met resultaten gaat het nu namelijk om andere data. Als er purchase orders zijn zonder sales order of vice versa, dan ziet je nieuwe resultaat er op zich niet verkeerd uit.
Ik vraag me alleen af wat je aan het doen met met die IS NOT NULL condities in combinatie met outer joins. Outer joins zijn er juist om NULL-results wel terug te krijgen.

Sorry voor de onduidelijkheid. Het is zo dat de resultaten anders zijn dan verwacht omdat vrijwel alles zonder sales order is. Dit kan natuurlijk niet aangezien we natuurlijk ook verkopen. Als ik nou een 100 ofzo zonder sales order had terug gekregen dan zou dat kunnen kloppen omdat die nog niet completed zijn. De reden waarom ik de is not null gebruikte was eigenlijk omdat dit voor mij de enigste bekende manier was om de sales/purchase order te limiteren.

code:

Laten we eens beginnen met alle bij elkaar horende sales- en purchase order id's bij elkaar te rapen. Dat gaan we doen met een FULL OUTER JOIN, ervan uitgaande dat er purchases zonder sales, maar ook sales zonder purchase kunnen zijn:

[code]SELECT COALESCE(pps_s.pps_serial, pps_p.pps_serial) as serial
     , pps_s.pps_salesorder_id as salesorder_id
     , pps_p.pps_purchaseorder_id as purchaseorder_id
           FROM purchase_product_serial as pps_s
FULL OUTER JOIN purchase_product_serial as pps_p
             ON pps_s.pps_serial = pps_p.pps_serial[/]

Wat we hebben is dus PPS gejoined met zichzelf op pps_serial. de COALESCE zorgt ervoor dat we die serial altijd zien, ook al kan (in ieder geval in theorie) een van de twee serials leeg zijn.

Dit is de basis voor je query. Als je hier inderdaad alle PPS_serials in teugziet met de bijbehorende purchase / sales order id's, dan kun je verder gaan.

Deze query heb ik even geprobeerd echter krijg ik onderstaande melding.

code:

[SQL]SELECT COALESCE(pps_s.pps_serial, pps_p.pps_serial) as serial
     , pps_s.pps_salesorder_id as salesorder_id
     , pps_p.pps_purchaseorder_id as purchaseorder_id
           FROM purchase_product_serial as pps_s
FULL OUTER JOIN purchase_product_serial as pps_p
             ON pps_s.pps_serial = pps_p.pps_serial

[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FULL OUTER JOIN purchase_product_serial as pps_p
             ON pps_s.pps_seri' at line 5

Als ik dat goed gelezen heb komt dit doordat er geen full outer join support is in (deze versie?) mysql.

Hier heb ik ook even heen zitten te zoeken en de manier om dit te emuleren zou via onderstaande code moeten zijn.

code:

SELECT t1.value, t2.value
FROM t1 LEFT OUTER JOIN t2 ON t1.value = t2.value
UNION ALL
SELECT t1.value, t2.value
FROM t2 LEFT OUTER JOIN t1 ON t1.value = t2.value
WHERE t1.value IS NULL

Hiermee zou ik ook duplicaten terug krijgen (union all) en als ik dat niet wil dan moet ik het all stukje weg halen. Dit moet ik wel even zien aan te passen naar mijn situatie. Als ik het goed begrijp zou ik dit dus 3x moeten doen. 1x tussen de inkoop en serial tabel en daarna verkoop en serial en als laatste tussen deze beide nog eens. Klopt die redenatie of mis ik iets daarin?

[Dido]

Yagermeister schreef op dinsdag 3 januari 2017 @ 10:36:

code:

\[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FULL OUTER JOIN purchase_product_serial as pps_p
             ON pps_s.pps_seri' at line 5

Als ik dat goed gelezen heb komt dit doordat er geen full outer join support is in (deze versie?) mysql.

Hier heb ik ook even heen zitten te zoeken en de manier om dit te emuleren zou via onderstaande code moeten zijn.

Ah, geen full outer join, dat is jammer. Dan zou ik me eerst even richten op één van deze twee:
• Alle purchase orders, met eventueel aanwezige sales order
OF
• Alle sales orders, met eventueel aanwezige purchase order

De eerste lijkt me de meest voor de hand liggende.

In het eerste geval vervang je FULL door LEFT, in het tweede geval door RIGHT. De rest zou moeten werken zoals beschreven.

Je krijgt dan twee queries voor keuze 1 en 2, en die overlappen voor het stuk waar een sales- aan een purchaseorder is gelinkt. Als je alles gescheiden wilt hebben is dat misschien wel het netste. In mijn eerste (basis)query kun je dit doen:

(A) Alles orders met gelinkte sales EN purchase:

code:

SELECT pps_s.pps_serial as serial
     , pps_s.pps_salesorder_id as salesorder_id
     , pps_p.pps_purchaseorder_id as purchaseorder_id
           FROM purchase_product_serial as pps_s
INNER JOIN purchase_product_serial as pps_p
             ON pps_s.pps_serial = pps_p.pps_serial

(B ) Alles sales orders zonder purchase order (kan dat?)

code:

SELECT pps_s.pps_serial as serial
     , pps_s.pps_salesorder_id as salesorder_id
     , pps_p.pps_purchaseorder_id as purchaseorder_id
           FROM purchase_product_serial as pps_s
LEFT OUTER JOIN purchase_product_serial as pps_p
             ON pps_s.pps_serial = pps_p.pps_serial
WHERE  pps_p.serial IS NULL

(C) Alle purchase orders zonder sale (dat heet voorraad?)

code:

SELECT COALESCE(pps_s.pps_serial, pps_p.pps_serial) as serial
     , pps_s.pps_salesorder_id as salesorder_id
     , pps_p.pps_purchaseorder_id as purchaseorder_id
           FROM purchase_product_serial as pps_p
LEFT OUTER JOIN purchase_product_serial as pps_s
             ON pps_p.pps_serial = pps_s.pps_serial
WHERE  pps_s.serial IS NULL

Die drie kun je verder uitbreiden, (A) kan helemaal zoals ik beschreef, (B ) en (C) ook maar dat heeft misschien minder zin.

Als je ze alle drie helemaal uitbreid op dezelfde manier kun je ze met een union aan elkaar knopen, maar misschien is dat helemaal niet handig. Het gaat immers om verkochte zaken die je ooit aangekocht had (A, normaal), aangekochte zaken die je nog niet verkocht hebt (C, voorraad) en verkochte zaken die nooit aangekocht zijn (B, fouten?).

Die drie zijn functioneel wezenlijk verschillend, dus wellicht zil je die in drie verschillende lijstjes zien...

P.S. Om iemand te quoten kun je de quote tag gebruiken in plaats van de code tag

[Yagermeister]

Dido schreef op dinsdag 3 januari 2017 @ 14:06:
[...]

Ah, geen full outer join, dat is jammer. Dan zou ik me eerst even richten op één van deze twee:
• Alle purchase orders, met eventueel aanwezige sales order
OF
• Alle sales orders, met eventueel aanwezige purchase order

De eerste lijkt me de meest voor de hand liggende.

In het eerste geval vervang je FULL door LEFT, in het tweede geval door RIGHT. De rest zou moeten werken zoals beschreven.

Je krijgt dan twee queries voor keuze 1 en 2, en die overlappen voor het stuk waar een sales- aan een purchaseorder is gelinkt. Als je alles gescheiden wilt hebben is dat misschien wel het netste. In mijn eerste (basis)query kun je dit doen:

(A) Alles orders met gelinkte sales EN purchase:

code:

SELECT pps_s.pps_serial as serial
     , pps_s.pps_salesorder_id as salesorder_id
     , pps_p.pps_purchaseorder_id as purchaseorder_id
           FROM purchase_product_serial as pps_s
INNER JOIN purchase_product_serial as pps_p
             ON pps_s.pps_serial = pps_p.pps_serial

(B ) Alles sales orders zonder purchase order (kan dat?)

code:

SELECT pps_s.pps_serial as serial
     , pps_s.pps_salesorder_id as salesorder_id
     , pps_p.pps_purchaseorder_id as purchaseorder_id
           FROM purchase_product_serial as pps_s
LEFT OUTER JOIN purchase_product_serial as pps_p
             ON pps_s.pps_serial = pps_p.pps_serial
WHERE  pps_p.serial IS NULL

(C) Alle purchase orders zonder sale (dat heet voorraad?)

code:

SELECT COALESCE(pps_s.pps_serial, pps_p.pps_serial) as serial
     , pps_s.pps_salesorder_id as salesorder_id
     , pps_p.pps_purchaseorder_id as purchaseorder_id
           FROM purchase_product_serial as pps_p
LEFT OUTER JOIN purchase_product_serial as pps_s
             ON pps_p.pps_serial = pps_s.pps_serial
WHERE  pps_s.serial IS NULL

Die drie kun je verder uitbreiden, (A) kan helemaal zoals ik beschreef, (B ) en (C) ook maar dat heeft misschien minder zin.

Als je ze alle drie helemaal uitbreid op dezelfde manier kun je ze met een union aan elkaar knopen, maar misschien is dat helemaal niet handig. Het gaat immers om verkochte zaken die je ooit aangekocht had (A, normaal), aangekochte zaken die je nog niet verkocht hebt (C, voorraad) en verkochte zaken die nooit aangekocht zijn (B, fouten?).

Die drie zijn functioneel wezenlijk verschillend, dus wellicht zil je die in drie verschillende lijstjes zien...

P.S. Om iemand te quoten kun je de quote tag gebruiken in plaats van de code tag

Ik heb de quote niet gebruikt omdat ik er eerlijk gezegt niet aan gedacht had .

Voor ons is een purchase zonder sales niet belangrijk dus dat stuk kan vervallen. Wij willen alleen weten wat daadwerkelijk verkocht is (op 1 maand en complete) en daaraan willen wij het serienummer met leverancier gekoppeld hebben. Als ik het goed begrijp is dat niet via een directe gang mogelijk maar alleen via 2 losse queries?

[Dido]

Yagermeister schreef op dinsdag 3 januari 2017 @ 18:59:
Ik heb de quote niet gebruikt omdat ik er eerlijk gezegt niet aan gedacht had .

Voor ons is een purchase zonder sales niet belangrijk dus dat stuk kan vervallen. Wij willen alleen weten wat daadwerkelijk verkocht is (op 1 maand en complete) en daaraan willen wij het serienummer met leverancier gekoppeld hebben. Als ik het goed begrijp is dat niet via een directe gang mogelijk maar alleen via 2 losse queries?

Jawel, als je pps_s (sales) LEFT JOIN pps_p (purchases) gebruikt, zonder de where xxx IS NULL dus, heb je alle sales, met een eventuele purchase. In 1 enkele query dus.

code:

SELECT pps_s.pps_serial as serial
     , pps_s.pps_salesorder_id as salesorder_id
     , pps_p.pps_purchaseorder_id as purchaseorder_id
           FROM purchase_product_serial as pps_s
LEFT OUTER JOIN purchase_product_serial as pps_p
             ON pps_s.pps_serial = pps_p.pps_serial

Uitbreiden zoals ik in mijn eerste post heb omschreven en het zou moeten werken

[Yagermeister]

Dido schreef op woensdag 4 januari 2017 @ 02:21:
[...]

Jawel, als je pps_s (sales) LEFT JOIN pps_p (purchases) gebruikt, zonder de where xxx IS NULL dus, heb je alle sales, met een eventuele purchase. In 1 enkele query dus.

code:

SELECT pps_s.pps_serial as serial
     , pps_s.pps_salesorder_id as salesorder_id
     , pps_p.pps_purchaseorder_id as purchaseorder_id
           FROM purchase_product_serial as pps_s
LEFT OUTER JOIN purchase_product_serial as pps_p
             ON pps_s.pps_serial = pps_p.pps_serial

Uitbreiden zoals ik in mijn eerste post heb omschreven en het zou moeten werken

Ik heb bovenstaande query even geprobeerd echter krijg ik nu nog steeds 2 regels? Dit zou toch nu de sales en purchases moeten samenvoegen tot 1 regel of niet?

[Dido]

Yagermeister schreef op donderdag 5 januari 2017 @ 16:51:
Ik heb bovenstaande query even geprobeerd echter krijg ik nu nog steeds 2 regels? Dit zou toch nu de sales en purchases moeten samenvoegen tot 1 regel of niet?

Yups, dat zou moeten. Ik zi enu echter ook waarom het niet werkt...

code:

SELECT pps_s.pps_serial as serial
     , pps_s.pps_salesorder_id as salesorder_id
     , pps_p.pps_purchaseorder_id as purchaseorder_id
           FROM purchase_product_serial as pps_s
LEFT OUTER JOIN purchase_product_serial as pps_p
             ON pps_s.pps_serial = pps_p.pps_serial
            AND pps_p.salesorder_id IS NULL

Zou beter moeten werken. Als het goed is krijg je nu alle purchase orders met de (als ie bestaat) de sales order.
De dubbelen kwamen door het feit dat de sales-order regels in purchase_order_serial ook weer voorkwamen als salesorder en dus zowel links als rechts van de join voorkwamen.

Een alternatief wat hetzelfde doet is

code:

SELECT pps_s.pps_serial as serial
     , pps_s.pps_salesorder_id as salesorder_id
     , pps_p.pps_purchaseorder_id as purchaseorder_id
           FROM purchase_product_serial as pps_s
LEFT OUTER JOIN purchase_product_serial as pps_p
             ON pps_s.pps_serial = pps_p.pps_serial
WHERE pps_s.purchaseorder_id IS NULL

of

code:

SELECT pps_s.pps_serial as serial
     , pps_s.pps_salesorder_id as salesorder_id
     , pps_p.pps_purchaseorder_id as purchaseorder_id
           FROM (purchase_product_serial as pps_s
          WHERE pps_s.purchaseorder_id IS NULL)
LEFT OUTER JOIN purchase_product_serial as pps_p
             ON pps_s.pps_serial = pps_p.pps_serial

Waarbij ik vermoed dat de laatsteversie het snelste is (als dat uitmaakt)

Mocht dat een issue zijn, dan moet je alle drie even testen (of de EXPLAIN bekijken).

[Yagermeister]

Dido schreef op donderdag 5 januari 2017 @ 20:30:
[...]

Yups, dat zou moeten. Ik zi enu echter ook waarom het niet werkt...

code:

SELECT pps_s.pps_serial as serial
     , pps_s.pps_salesorder_id as salesorder_id
     , pps_p.pps_purchaseorder_id as purchaseorder_id
           FROM purchase_product_serial as pps_s
LEFT OUTER JOIN purchase_product_serial as pps_p
             ON pps_s.pps_serial = pps_p.pps_serial
            AND pps_p.salesorder_id IS NULL

Zou beter moeten werken. Als het goed is krijg je nu alle purchase orders met de (als ie bestaat) de sales order.
De dubbelen kwamen door het feit dat de sales-order regels in purchase_order_serial ook weer voorkwamen als salesorder en dus zowel links als rechts van de join voorkwamen.

Een alternatief wat hetzelfde doet is

code:

SELECT pps_s.pps_serial as serial
     , pps_s.pps_salesorder_id as salesorder_id
     , pps_p.pps_purchaseorder_id as purchaseorder_id
           FROM purchase_product_serial as pps_s
LEFT OUTER JOIN purchase_product_serial as pps_p
             ON pps_s.pps_serial = pps_p.pps_serial
WHERE pps_s.purchaseorder_id IS NULL

of

code:

SELECT pps_s.pps_serial as serial
     , pps_s.pps_salesorder_id as salesorder_id
     , pps_p.pps_purchaseorder_id as purchaseorder_id
           FROM (purchase_product_serial as pps_s
          WHERE pps_s.purchaseorder_id IS NULL)
LEFT OUTER JOIN purchase_product_serial as pps_p
             ON pps_s.pps_serial = pps_p.pps_serial

Waarbij ik vermoed dat de laatsteversie het snelste is (als dat uitmaakt)

Mocht dat een issue zijn, dan moet je alle drie even testen (of de EXPLAIN bekijken).

Ik heb bovenstaande geprobeerd maar hij blijft me dubbele regels geven. Nu kan ik me best voorstellen dat er een foutje gemaakt is maar ik zou het zeer sterk vinden als dit 3 of meer keer achter elkaar is gebeurt.

Dus om dit uit te sluiten heb ik de gewenste data eens naar 2 losse excel files geexporteerd en toen via vert.zoeken aan elkaar gekoppeld. Het blijkt nu dat er maar welgeteld 20 records zijn met dezelfde identieke regels. Laat dit nou ook net de regels zijn die ik in een eerdere post ook al in 1 regel had staan wat ik raar vondt.

Na wat meer zoeken merkte ik op dat sommige serienummers een extra spatie of enter in de tekst hebben staan (mogelijk door de verschillende scanners waarmee dit gescanned is geworden). Hierdoor lijkt het me ook vrij lastig om deze aan elkaar te koppelen aangezien de inhoud tenslotte ook anders is. Het probleem is dat ik hier echter geen aanpassingen in kan doen waardoor dit niet meer zou moeten voorkomen.

Met wat aanpassingen kan ik wel de eerste query gebruiken om de regels dubbel te krijgen zodat we in ieder geval niet meer zoveel zoekwerk hebben in het hele gedeelte. Zou je alsnog een oplossing weten dan hoor ik het natuurlijk graag.

[Kontsnorretje]

Wat je zou kunnen doen is iets in de trand van REPLACE(TRIM(pp_s.pps_serial), "\r\n", "") om de spaties/enters eruit te strippen.

Dan krijg je zoiets

code:

SELECT pps_s.pps_serial as serial
     , pps_s.pps_salesorder_id as salesorder_id
     , pps_p.pps_purchaseorder_id as purchaseorder_id
           FROM purchase_product_serial as pps_s
LEFT OUTER JOIN purchase_product_serial as pps_p
             ON REPLACE(TRIM(pp_s.pps_serial), "\r\n", "") = REPLACE(TRIM(pp_p.pps_serial), "\r\n", "")
             AND pps_p.salesorder_id IS NULL

Zo kan je toch een join doen ondanks dat de waardes iets afwijken. Mocht dit niet genoeg zijn, heeft MySQL nog meer functies om strings te manipuleren:
http://dev.mysql.com/doc/refman/5.7/en/string-functions.html

[Yagermeister]

Kontsnorretje schreef op vrijdag 6 januari 2017 @ 22:04:
Wat je zou kunnen doen is iets in de trand van REPLACE(TRIM(pp_s.pps_serial), "\r\n", "") om de spaties/enters eruit te strippen.

Dan krijg je zoiets

code:

SELECT pps_s.pps_serial as serial
     , pps_s.pps_salesorder_id as salesorder_id
     , pps_p.pps_purchaseorder_id as purchaseorder_id
           FROM purchase_product_serial as pps_s
LEFT OUTER JOIN purchase_product_serial as pps_p
             ON REPLACE(TRIM(pp_s.pps_serial), "\r\n", "") = REPLACE(TRIM(pp_p.pps_serial), "\r\n", "")
             AND pps_p.salesorder_id IS NULL

Zo kan je toch een join doen ondanks dat de waardes iets afwijken. Mocht dit niet genoeg zijn, heeft MySQL nog meer functies om strings te manipuleren:
http://dev.mysql.com/doc/refman/5.7/en/string-functions.html

Ik heb dit gisteren nog even geprobeerd maar geen verschil gezien in de uitkomst. Zodra ik even tijd heb ga ik eens kijken of het misschien geen optie is om dit via de linux commandline en awk om te zetten naar iets werkbaars. Mogelijk kan ik ze dan wel rechtrekken.

[Yagermeister]

Volgens mij heb ik het probleem gevonden. Het blijkt dat 1 van onze barcode scanners een return extra meegeeft waardoor dus extra karakters in de cel staan. Is er een makkelijke manier om deze eruit te filteren mits die erin staat?

[Kontsnorretje]

Yagermeister schreef op maandag 9 januari 2017 @ 15:29:
Volgens mij heb ik het probleem gevonden. Het blijkt dat 1 van onze barcode scanners een return extra meegeeft waardoor dus extra karakters in de cel staan. Is er een makkelijke manier om deze eruit te filteren mits die erin staat?

Dan zou de truc uit m'n vorige post moeten werken. Misschien dat ik de verkeerde query uit het topic heb gekopiëerd?

code:

SELECT pps_s.pps_serial as serial
     , pps_s.pps_salesorder_id as salesorder_id
     , pps_p.pps_purchaseorder_id as purchaseorder_id
           FROM purchase_product_serial as pps_s
LEFT OUTER JOIN purchase_product_serial as pps_p
             ON REPLACE(TRIM(pp_s.pps_serial), "\r\n", "") = REPLACE(TRIM(pp_p.pps_serial), "\r\n", "")
             AND pps_p.salesorder_id IS NULL

Dan zou je in dit stukje misschien even moeten knutselen om de enters te vervangen:

code:

#Dit veranderen
ON REPLACE(TRIM(pp_s.pps_serial), "\r\n", "") = REPLACE(TRIM(pp_p.pps_serial), "\r\n", "")

#In:
#Of zonder trim
ON REPLACE(pp_s.pps_serial, "\r\n", "") = REPLACE(pp_p.pps_serial, "\r\n", "")
#Of andere enters
ON REPLACE(pp_s.pps_serial, "\r", "") = REPLACE(pp_p.pps_serial, "\r", "")
#Of andere enters
ON REPLACE(pp_s.pps_serial, "\n", "") = REPLACE(pp_p.pps_serial, "\n", "")

#Als andere velden andere enters hebben
ON REPLACE(pp_s.pps_serial, "\n", "") = REPLACE(pp_p.pps_serial, "\r\n", "")

Ik weet niet exact hoe je data eruit ziet, maar een van deze varianten zou toch moeten werken.

[ Voor 3% gewijzigd door Kontsnorretje op 09-01-2017 21:00 ]

[Yagermeister]

Kontsnorretje schreef op maandag 9 januari 2017 @ 20:58:
[...]


Dan zou de truc uit m'n vorige post moeten werken. Misschien dat ik de verkeerde query uit het topic heb gekopiëerd?

code:

SELECT pps_s.pps_serial as serial
     , pps_s.pps_salesorder_id as salesorder_id
     , pps_p.pps_purchaseorder_id as purchaseorder_id
           FROM purchase_product_serial as pps_s
LEFT OUTER JOIN purchase_product_serial as pps_p
             ON REPLACE(TRIM(pp_s.pps_serial), "\r\n", "") = REPLACE(TRIM(pp_p.pps_serial), "\r\n", "")
             AND pps_p.salesorder_id IS NULL

Dan zou je in dit stukje misschien even moeten knutselen om de enters te vervangen:

code:

#Dit veranderen
ON REPLACE(TRIM(pp_s.pps_serial), "\r\n", "") = REPLACE(TRIM(pp_p.pps_serial), "\r\n", "")

#In:
#Of zonder trim
ON REPLACE(pp_s.pps_serial, "\r\n", "") = REPLACE(pp_p.pps_serial, "\r\n", "")
#Of andere enters
ON REPLACE(pp_s.pps_serial, "\r", "") = REPLACE(pp_p.pps_serial, "\r", "")
#Of andere enters
ON REPLACE(pp_s.pps_serial, "\n", "") = REPLACE(pp_p.pps_serial, "\n", "")

#Als andere velden andere enters hebben
ON REPLACE(pp_s.pps_serial, "\n", "") = REPLACE(pp_p.pps_serial, "\r\n", "")

Ik weet niet exact hoe je data eruit ziet, maar een van deze varianten zou toch moeten werken.

Ik ga dit proberen zodra ik even tijd krijg hiervoor.

Ik heb trouwens een voorbeeld van de data in de eerste post staan en in 1 van de andere staat een overzicht van de tabellen als je die zoekt .