Subquery gelinkt aan hoofdquery

[spank_mojoo]

Ik heb een tabel welke er als volgt uit ziet;

code:

Code        Description Warehouse   Stock
20032       speelkaarten set a 52   Warehouse01     100
20032       speelkaarten set a 52   Warehouse02     50
20032       speelkaarten set a 52   Warehouse03     52
99999       Dammen voor gevorderden Warehouse01     2
99999       Dammen voor gevorderden Warehouse02     99
99999       Dammen voor gevorderden Warehouse03     24
54321       Indoor Tikkertje    Warehouse01     24
54321       Indoor Tikkertje    Warehouse02     61
54321       Indoor Tikkertje    Warehouse03     13

Voor een rapportage zou ik deze graag weergeven als;

code:

Code    Description Stock_Warehouse01   Stock_Warehouse02   Stock_Warehouse03
20032       Speelkaarten set a 52   100 50  52
99999       Dammen voor gevorderden 2   99  24
54321       Indoor Tikkertje    24  61  13

Relevante software en hardware die ik gebruik
Microsoft SQL Server 2014 (SP2-CU4) (KB4010394) - 12.0.5540.0 (X64)
Notepad++

Wat ik al gevonden of geprobeerd heb:
Voor zover ik weet kan ik dit alleen bewerkstelligen met een subquery, maar ik krijg het maar niet voor elkaar.
Ik heb nu de volgende (vereenvoudigde) code;

code:

Select distinct
Code,
Description,

(select sum(stock) from  article where code=code and warehouse = 'Warehouse01_')
From  Article

Maar deze subquery sommeert de Stockvan alle artikelen.
Wat doe ik verkeerd?

...

[Jory]

Door de "code=code" in de subquery, wordt het code veld dus met zichzelf vergeleken.

Wat je kunt doen, is aliassen gebruiken om duidelijk te maken "welke" code je bedoelt.

code:

SELECT DISTINCT
a.Code,
a.Description,
(SELECT SUM(stock) FROM Article AS aw1 WHERE a.code=aw1.code AND warehouse = 'Warehouse01') AS Stock_Warehouse01
FROM Article AS a

In deze query refereert a.Code dus naar de "code in de hoofdquery" en aw1.Code naar "code in de subquery".

Dit betekend overigens wel dat je danwel gaan hardcoden hoeveel warehouses je hebt, danwel de query dynamisch moet opbouwen.

Een andere optie zou kunnen zijn om GROUP BY te combineren met GROUP_CONCAT() STUFF(). Dan is de query altijd hetzelfde, ongeacht het aantal warehouses. Moet je alleen in de code nog het resultaat van de concat weer opsplitsen.

[ Voor 6% gewijzigd door Jory op 20-10-2017 16:18 ]

[Muuh87]

Ik denk dat je dit beter met een PIVOT kan doen in plaats van via een subquery.

code:

create table #article (Code int, description varchar(50), warehouse varchar(20), stock int)

insert into #article values (20032,'speelkaarten set a 52', 'Warehouse01', 100)
insert into #article values (20032,'speelkaarten set a 52', 'Warehouse02', 50)
insert into #article values (20032,'speelkaarten set a 52', 'Warehouse03', 52)

insert into #article values (99999,'Dammen voor gevorderden', 'Warehouse01', 2)
insert into #article values (99999,'Dammen voor gevorderden', 'Warehouse02', 99)
insert into #article values (99999,'Dammen voor gevorderden', 'Warehouse03', 24)

insert into #article values (54321,'Indoor tikkertje', 'Warehouse01', 24)
insert into #article values (54321,'Indoor tikkertje', 'Warehouse02', 61)
insert into #article values (54321,'Indoor tikkertje', 'Warehouse03', 13)

select *
from (
select code, description, stock, warehouse
from #article
) a
pivot
(
 sum(stock)
 for warehouse in (Warehouse01,Warehouse02,Warehouse03)
) as pvt
order by code

drop table #article

[ Voor 0% gewijzigd door Muuh87 op 20-10-2017 16:19 . Reden: code tags toevoegen ]

[RobIII]

Je bent op zoek naar PIVOT.

[spank_mojoo]

Ik ben maandag weer op het werk, zal er dan verder naar kijken.
ik neem aan dat de manier van indelen in middels deze Pivot enigzins gelijk is aan de Pivots in Excel?

[spank_mojoo]

Het is een dagje uitgelopen,
adhv de foutmeldingen die ik krijg op het gebruik van PIVOT krijg ik het idee dat deze functie een nieuwe tabel poogt aan te maken in de database. Ik heb geen rechten om deze database te wijzigen dus deze optie valt af voor mij.
tijdens een zoektocht op diverse websites kwam ik een dubbele join tegen met dezelfde tabel.

deze heb ik als volgt geimplementeerd;

code:

select Art1.code, Art1.description, Art2.stock AS "Warehouse01", Art3.stock AS "Warehouse02"
from article Art1
Inner JOIN article Art2
            ON Art1.code = art2.code
Inner JOIN  article Art3
            ON Art1.code = art3.code
WHERE Art2.warehouse='Warehouse01'  and Art3.warehouse='Warehouse02'
Order By Art1.Code

deze query geeft mij bijna het gewenste resultaat
het probleem dat ik nu nog moet overkomen is dat artikelen die wel voorkomen in warehouse01, maar niet in voorkomen in Warehouse02 nu niet worden weergegeven.

in ieder geval reuze bedankt tot zover allemaal

[PB-powell]

spank_mojoo schreef op dinsdag 24 oktober 2017 @ 12:12:
Het is een dagje uitgelopen,
adhv de foutmeldingen die ik krijg op het gebruik van PIVOT krijg ik het idee dat deze functie een nieuwe tabel poogt aan te maken in de database. Ik heb geen rechten om deze database te wijzigen dus deze optie valt af voor mij.
tijdens een zoektocht op diverse websites kwam ik een dubbele join tegen met dezelfde tabel.

deze heb ik als volgt geimplementeerd;

code:

select Art1.code, Art1.description, Art2.stock AS "Warehouse01", Art3.stock AS "Warehouse02"
from article Art1
Inner JOIN article Art2
            ON Art1.code = art2.code
Inner JOIN  article Art3
            ON Art1.code = art3.code
WHERE Art2.warehouse='Warehouse01'  and Art3.warehouse='Warehouse02'
Order By Art1.Code

deze query geeft mij bijna het gewenste resultaat
het probleem dat ik nu nog moet overkomen is dat artikelen die wel voorkomen in warehouse01, maar niet in voorkomen in Warehouse02 nu niet worden weergegeven.

in ieder geval reuze bedankt tot zover allemaal

Ik ben geen super grote database expert, maar met de query kreeg ik het resultaat wat je zocht:

code:

select art.code, art.description,
WH1.stock,
WH2.stock,
WH3.stock
from article art
join (select code, stock from article where warehouse = 'Warehouse01') WH1 on WH1.code = art.code
join (select code, stock from article where warehouse = 'Warehouse02') WH2 on WH2.code = art.code
join (select code, stock from article where warehouse = 'Warehouse03') WH3 on WH3.code = art.code
group by 1,2,3,4,5;

Het resultaat was dit:

code:

code    description                       stock1    stock2  stock3
20032   speelkaarten set a 5             100            50          52
54321   Indoor Tikkertje                  24            61          13
99999   Dammen voor gevorderden  2          99          24

Dit is overigens wel geschreven voor MySQL, dus of het een-op-een te gebruiken is in MS SQL weet ik niet zeker.

[MSteverink]

spank_mojoo schreef op dinsdag 24 oktober 2017 @ 12:12:
het probleem dat ik nu nog moet overkomen is dat artikelen die wel voorkomen in warehouse01, maar niet in voorkomen in Warehouse02 nu niet worden weergegeven.

LEFT JOIN, ipv INNER JOIN

[DonnyR]

Je zou het als volgt kunnen doen.

Met een subquery

code:

SELECT Code, 
    Description, 
    Stock as Stock_Warehouse01, 
    (SELECT Stock FROM Article a2 WHERE a.Code = a2.Code AND a2.Warehouse = 'Warehouse02') as Stock_Warehouse02, 
    (SELECT Stock FROM Article a2 WHERE a.Code = a2.Code AND a2.Warehouse = 'Warehouse03') as Stock_Warehouse03
FROM Article a
WHERE a.Warehouse = 'Warehouse01'

Met een join:

code:

SELECT a1.Code, 
    a1.Description, 
    a1.Stock as Stock_Warehouse01, 
    a2.Stock AS Stock_Warehouse02, 
    a3.Stock AS Stock_Warehouse03
FROM Article a1
LEFT JOIN Article a2 ON a1.Code = a2.Code AND a2.Warehouse = 'Warehouse02'
LEFT JOIN Article a3 ON a1.Code = a3.Code AND a3.Warehouse = 'Warehouse03'
WHERE a1.Warehouse = 'Warehouse01'

[ Voor 35% gewijzigd door DonnyR op 24-10-2017 14:58 ]

[spank_mojoo]

That's it!
dmv deze Join loopt het inderdaad.
dom dat ik hier de verkeerde JOIN voor heb gebruikt

[MSteverink]

Ik bedenk me nu...
Je moet even testen wat er gebeurt wanneer een artikel in Warehouse 01 niet voorkomt.

[spank_mojoo]

Dankje, daar zal ik nog naar kijken.
In Theorie zou dit niet voor moeten kunnen komen, maar voorkomen is beter dan genezen.

[Gomez12]

In principe zou ik ook zeggen pivot, maar ls je daar niet uitkomt dan :

DonnyR schreef op dinsdag 24 oktober 2017 @ 14:49:
Met een join:

code:

SELECT a.Code, 
    a.Description, 
    a1.Stock as Stock_Warehouse01, 
    a2.Stock AS Stock_Warehouse02, 
    a3.Stock AS Stock_Warehouse03
FROM Article a
LEFT JOIN Article a1 ON a.Code = a1.Code AND a1.Warehouse = 'Warehouse01'
LEFT JOIN Article a2 ON a.Code = a2.Code AND a2.Warehouse = 'Warehouse02'
LEFT JOIN Article a3 ON a.Code = a3.Code AND a3.Warehouse = 'Warehouse03'

zou ik hem wel zoals bovenstaand implementeren. Dan maak je echt een onderscheid tussen de artikelen die je wilt zien (dat geef je op in de where a) en haal je separaat stock op per warehouse.
Zo voorkom je dat nu alles in warehouse 1 ligt en mogelijk ook in warehouse 2 of 3 (waarvoor de eerdere query ook werkt) alleen dat hij breekt als je later spullen niet meer per definitie gaat onderbrengen in warehouse 1.

[Muuh87]

spank_mojoo schreef op dinsdag 24 oktober 2017 @ 12:12:
Het is een dagje uitgelopen,
adhv de foutmeldingen die ik krijg op het gebruik van PIVOT krijg ik het idee dat deze functie een nieuwe tabel poogt aan te maken in de database. Ik heb geen rechten om deze database te wijzigen dus deze optie valt af voor mij.
tijdens een zoektocht op diverse websites kwam ik een dubbele join tegen met dezelfde tabel.

deze heb ik als volgt geimplementeerd;

code:

select Art1.code, Art1.description, Art2.stock AS "Warehouse01", Art3.stock AS "Warehouse02"
from article Art1
Inner JOIN article Art2
            ON Art1.code = art2.code
Inner JOIN  article Art3
            ON Art1.code = art3.code
WHERE Art2.warehouse='Warehouse01'  and Art3.warehouse='Warehouse02'
Order By Art1.Code

deze query geeft mij bijna het gewenste resultaat
het probleem dat ik nu nog moet overkomen is dat artikelen die wel voorkomen in warehouse01, maar niet in voorkomen in Warehouse02 nu niet worden weergegeven.

in ieder geval reuze bedankt tot zover allemaal

Klopt, het voorbeeld maakte een tijdelijke tabel aan. Dit omdat ik niet de preciese tabellen kende en zo het restultaat kon bekijkem😎😉Als je die weglaat en bij de select en poot jouw tabellen en kolommen zet zal het werken. Maar zie dat je t al met joins voor elkaar hebt.