Information générale
Catégorie:
Base de données, Tables, Vues, Index et syntaxe SQL
Titre:
Avoiding redundant data in a view
I have a view that includes parent child relations.
The following sql statement duplicates Cms (the parent) data if there are more than one Items (child) records.
In this scenario, how can the statement be changed so that parent (cms) information is shown only once when there are multiple child records? In other words, nulls would appear in the cms part of the view for the second, third, fourth, etc. child records. Thanks in advance!
SELECT DISTINCT Cms.order, Cms.custnum, Cms.odr_date, Cms.paymethod,;
RIGHT(ALLTRIM(Cms.cardnum),4), Cms.exp, Cms.approval, Items.order,;
Items.item, Items.inpart, Items.quanto, Items.quantf, Items.quantb,;
Items.quantp, Items.quants, Items.discount, Items.picked, Items.desc,;
Items.item_state, Items.ponumber, Box.order, Box.box, Box.status,;
Box.trackingno, Invoice.order, Invoice.inpart, Invoice.custnum,;
Invoice.shipping, Invoice.ship_date, Invoice.inv_date, Invoice.amt_paid,;
Invoice.amount, Invoice.stax;
FROM ;
export!cms ;
INNER JOIN export!items ;
ON Cms.order = Items.order ;
INNER JOIN export!box ;
ON Cms.order = Box.order ;
INNER JOIN export!invoice ;
ON Cms.order = Invoice.order;
WHERE NOT (EMPTY(Items.item) );
ORDER BY Cms.order, Items.item
Suivant
Répondre
Voir le fil de ce thread
Voir le fil de ce thread à partir de ce message seulement
Voir tous les messages de ce thread
Voir tous les messages de ce thread à partir de ce message seulement