[MYSQL] Weergeven als er geen gerelateerde records zijn

[.Alex]

Goedemorgen!

Ik wil graag alle records uit de tabel "product" weergeven, waarvan geen relateerde records bestaan in de tabel "voorraad". Als er wel een record bestaat, moet het veld "voorraad.waarde" de waarde "0" hebben.

Mijn tabellen:

Tabel: product
- product_id

Tabel: voorraad
- FK_product_id
- voorraad

Het hele idee is dus dat ik alle producten wil weergeven waar geen voorraad meer van is. Records uit voorraad worden zo af en toe opgeruimd, alle records met waarde=0 worden verwijderd. Dit puur om performance-redenen, het is niet handig als er 8000 extra records in de tabel staan.

Kan iemand me hier helpen?

[kenneth]

Hoe ziet de query eruit die je zelf geprobeerd hebt?

[CrashOne]

SELECT *
FROM product
WHERE NOT Exists (SELECT * FROM voorraad WHERE voorraad.FK_product_id = product.product_id);

Zoiets? niet getest

[4VAlien]

delete from product where product_id NOT in (Select FK_product_id from voorraad )

zoiets?

[Ascathon]

SQL:

SELECT product.product_id
FROM product
WHERE product.product_id NOT IN
(select FK_product_id
from voorraad)
UNION
SELECT product.product_id
FROM product
WHERE product.product_id = voorraad.FK_product_id
AND voorraad.voorraad = 0;

Dus eerst product_id die niet in de andere tabel voorkomt, en dan de product_id waar voorraad 0 bij hoort. Zover ik weet zorgt UNION er zelf voor dat je geen dubbele product.product_id krijgt.
SQL validator keurt t goed Graag comments...

[ Voor 38% gewijzigd door Ascathon op 16-02-2006 10:56 ]

[TeeDee]

.Alex schreef op donderdag 16 februari 2006 @ 10:10:
Het hele idee is dus dat ik alle producten wil weergeven waar geen voorraad meer van is. Records uit voorraad worden zo af en toe opgeruimd, alle records met waarde=0 worden verwijderd. Dit puur om performance-redenen, het is niet handig als er 8000 extra records in de tabel staan.

Dus als een product niet meer in voorraad is, komt het product ook niet meer terug?
Puur interesse: waarom wil je dit verwijderen?

8000 records in db maakt toch geen reet uit?

[NMe]

MySQL kent geen subqueries in versies voor 4.1. Dit zou wel moeten werken:

SQL:

SELECT p.product_id
FROM product AS p LEFT JOIN voorraad AS v ON p.product_id = v.FK_product_id
WHERE (v.FK_product_id IS NULL) OR (v.FK_product_id = 0)

Ascathon schreef op donderdag 16 februari 2006 @ 10:24:
Zover ik weet zorgt UNION er zelf voor dat je geen dubbele product.product_id krijgt.
SQL validator keurt t goed Graag comments...

Dat een validator iets goedkeurt wil niet zeggen dat iets werkt, al doet dit het waarschijnlijk wel. UNION an sich werkt trouwens geen dubbele records weg, daarvoor moet je expliciet aangeven dat het moet gebeuren met UNION DISTINCT.

Ik ben het trouwens met TeeDee eens dat 8000 records niks voorstelt, en het heel raar is om om die reden producten te verwijderen die niet meer in voorraad zijn. Zelfs als ze niet meer op voorraad komen is het beter om deze producten niet te verwijderen, aangezien de facturentabel (als die bestaat) er waarschijnlijk nog verwijzingen naar heeft.

[Ascathon]

-NMe- schreef op donderdag 16 februari 2006 @ 10:47:
UNION an sich werkt trouwens geen dubbele records weg, daarvoor moet je expliciet aangeven dat het moet gebeuren met UNION DISTINCT.

Quote van http://www.1keydata.com/sql/sqlunion.html

Also, when using UNION, only distinct values are selected

---

8000 records in db maakt toch geen reet uit?

Hier op stage tabellen met 18.000 records per tabel. Search op alle honderd tabellen (met een LIKE op alle kolommen) is zo gebeurd.

[ Voor 36% gewijzigd door Ascathon op 16-02-2006 11:00 ]

[.Alex]

-NMe- schreef op donderdag 16 februari 2006 @ 10:47:
MySQL kent geen subqueries in versies voor 4.1. Dit zou wel moeten werken:

SQL:

SELECT p.product_id
FROM product AS p LEFT JOIN voorraad AS v ON p.product_id = v.FK_product_id
WHERE (v.FK_product_id IS NULL) OR (v.FK_product_id = 0)

[...]

Dat een validator iets goedkeurt wil niet zeggen dat iets werkt, al doet dit het waarschijnlijk wel. UNION an sich werkt trouwens geen dubbele records weg, daarvoor moet je expliciet aangeven dat het moet gebeuren met UNION DISTINCT.

Ik ben het trouwens met TeeDee eens dat 8000 records niks voorstelt, en het heel raar is om om die reden producten te verwijderen die niet meer in voorraad zijn. Zelfs als ze niet meer op voorraad komen is het beter om deze producten niet te verwijderen, aangezien de facturentabel (als die bestaat) er waarschijnlijk nog verwijzingen naar heeft.

Dit werkt echt prima, thnx! Ik wist niet dat "IS NULL" bestond in SQL

Even voor de duidelijkheid, de voorraad-tabel wordt telkens "opgeschoont". Er worden ook maten bijgehouden in de voorraadtabel. 80% van de records in onzinnig, omdat de voorraad van de producten daar "0" is, en het product nooit meer in de desbetreffende maat geleverd zal worden. Omdat het assortiment nogal eens wijzigt, is het niet handig om allemaal records in de voorraadtabel te laten staan. 8000 records is best te overzien, maar dat is het geval als ik 1 keer per maand de tabel "opschoon". Op jaarbasis zijn dit er dus een stuk meer.

Ik zal binnenkort waarschijnlijk nog wel een topic openen over het voorraadsysteem, dat is absoluut niet handig ingericht momenteel

[NMe]

Ascathon schreef op donderdag 16 februari 2006 @ 10:54:
Quote van http://www.1keydata.com/sql/sqlunion.html

Hmm, je hebt gelijk zie ik:

quote: http://dev.mysql.com/doc/refman/5.0/en/union.html

The default behavior for UNION is that duplicate rows are removed from the result. The optional DISTINCT keyword has no effect other than the default because it also specifies duplicate-row removal.

Hier op stage tabellen met 18.000 records per tabel. Search op alle honderd tabellen (met een LIKE op alle kolommen) is zo gebeurd.

18.000 records is dan ook nog steeds niks. 18.000.000 records zou zelfs nog relatief snel gaan. 100 tabellen klinkt trouwens wel erg smerig...òf het is een gigantische app, òf er is niet goed genormaliseerd.

[Ascathon]

Mja, kan/mag er verder niks over vertellen. Alleen dat het een voorlopige snelle oplossing was, dus dat er absoluut niet gekeken is naar normalisatie.

[ Voor 23% gewijzigd door Ascathon op 16-02-2006 13:23 ]