CODEWITHSUNDEEP
mysqlsql
user2639995
user2639995

Reputation: 1

SQL Query (inner join, and duplicate results)

I'm completely new to SQL, just trying to speed up my job by making a few queries that can help me.

My example code (I restricted the query to one order, to show the problem):

SELECT dbo.BI.nmdos, dbo.BI.OBRANO, dbo.bi.ref
, dbo.BI.design, dbo.BI.LOBS, dbo.BI.qtt, dbo.sa.stock
FROM dbo.BI
LEFT JOIN dbo.SA on dbo.SA.REF=dbo.BI.REF
WHERE dbo.BI.FECHADA=0 AND dbo.BI.LOBS <> 'S' 
AND dbo.BI.QTT >= 1 AND dbo.BI.nmdos = 'Encomenda Cliente' 
AND dbo.BI.obrano = 2496

My result:

Encomenda Cliente   2496                        rty     5.0000  NULL
Encomenda Cliente   2496    01103402            TONER OKI P/B4000       1.0000  0.000
Encomenda Cliente   2496    PC                  PORTES - COMPRAS        2.0000  1429.000
Encomenda Cliente   2496    CXDVDNORMBL         CAIXA DE DVD NORMAL PRETO FOSCO - 14MM (100)        10.0000 4.000
Encomenda Cliente   2496    CXDVDNORMBL         CAIXA DE DVD NORMAL PRETO FOSCO - 14MM (100)        10.0000 0.000
Encomenda Cliente   2496    CXDVDNORMBL         CAIXA DE DVD NORMAL PRETO FOSCO - 14MM (100)        10.0000 0.000

My problem: I need to remove the last 2 lines with the product code "CXDVDNORMBL" that have stock = 0.

What should I do?

Ty

Upvotes: 0

Views: 98

Answers (2)

vanamerongen
vanamerongen

Reputation: 837

Do you mean you want to delete rows from an order when those products are out of stock? Just run another query that deletes the same thing that you selected before but add WHERE stock = 0?

Not sure it's wise to completely remove those rows though. You might need that information later, for instance if you're going to send an email to the customer saying some items can't be included in their order, or just to log what customers have ordered (can still be very relevant information even if it was never delivered). Therefore I think it might be better to just filter out any rows with 0 stock whenever you do a select for it. Or, if you really want those rows out of the orders table for some reason, put them in another table called outOfStock with the order ID linked to it.

Upvotes: 0

Sethcran
Sethcran

Reputation: 788

You need to add dbo.SA.stock > 0 to your join condition. This way it will only join on rows from SA that have some stock.

SELECT dbo.BI.nmdos, dbo.BI.OBRANO, dbo.bi.ref, dbo.BI.design, dbo.BI.LOBS, dbo.BI.qtt, dbo.sa.stock
FROM dbo.BI
LEFT JOIN dbo.SA on dbo.SA.REF=dbo.BI.REF AND dbo.SA.stock > 0
WHERE dbo.BI.FECHADA=0 AND dbo.BI.LOBS<>'S' AND dbo.BI.QTT>=1 AND dbo.BI.nmdos='Encomenda Cliente' AND dbo.BI.obrano=2496

Upvotes: 1

Related Questions