Prev: non-equal DRI: a possible new sql feature
Next: Change management - views that select data from tables in diff
From: Savvoulidis Iordanis on 4 Mar 2010 10:23
Hi.
I have an Orders database (orders, products, orderlines tables).
What I want to write is, a select statement that, when there is an incoming
order (containing let's say 4 products), it shows all other orders that
contain at least those products the incoming order contains, or more.

Which should be the proper table indexing choice, for the above scenario?

TIA
Iordanis
From: Uri Dimant on 4 Mar 2010 10:32
Can you please provide DDL+ sample data+ an expected result?

BTW , you are running SQL Server 2005/2008?



"Savvoulidis Iordanis" <SavvoulidisIordanis(a)discussions.microsoft.com> wrote
in message news:733FBE0A-D686-4D5D-BBEA-D8E92780229D(a)microsoft.com...
> Hi.
> I have an Orders database (orders, products, orderlines tables).
> What I want to write is, a select statement that, when there is an
> incoming
> order (containing let's say 4 products), it shows all other orders that
> contain at least those products the incoming order contains, or more.
>
> Which should be the proper table indexing choice, for the above scenario?
>
> TIA
> Iordanis


From: --CELKO-- on 4 Mar 2010 11:09
>> I have an Orders database (Orders, Products, Order_Details tables). What I want to write is, a select statement that, when there is an incoming order (containing let's say 4 products), it shows all other orders that contain at least those products the incoming order contains, or more.<<

This is called a Relational Division; you can Google that. Since you
did not bother to post any DDL, we can only guess. Indexing is
probably not as important as DRI. The keys will give you indexes.

CREATE TABLE Orders
(order_nbr CHAR(9) NOT NULL PRIMARY KEY,
...);

CREATE TABLE Order_Details
(order_nbr CHAR(9) NOT NULL
REFERENCES Orders(order_nbr)
ON DELETE CASCADE
ON UPDATE CASCADE,
product_nbr CHAR(15) NOT NULL -- GTIN or other industry standards?
REFERENCES Products(product_nbr)
ON DELETE CASCADE
ON UPDATE CASCADE,
PRIMARY KEY (order_nbr, product_code),
product_qty INTEGER NOT NULL
CHECK (order_qty > 0),
..);

CREATE TABLE Products
(product_nbr CHAR(15) NOT NULL -- GTIN or other industry standards?
PRIMARY KEY,
..);

Notice that I changed Order_lines to Order_Details. We do not model
the lines on a PHYSICAL paper form in RDBMS, so you need to use the
proper business term. Your specs do not say if an order with a
different quantity of a product is a match or not.






>
> Which should be the proper table indexing choice, for the above scenario?
>
> TIA
> Iordanis

From: Savvoulidis Iordanis on 4 Mar 2010 11:26
SQL Server 2005. Not in a position to extract any DML right now, but I
believe it's the most classic scenario for anybody in the DB field. So I'd
rather describe it a bit more.

Say, incoming Order (ID=100) contains products with IDs=10,11,12,13 in the
OrderDetails table.
If I have other orders also containing ALL the above product IDs among other
products, I want to display them. That is:

Order (ID=101) with product IDs (10,11,12,13,14,15) must be displayed
Order (ID=102 with product IDs (10,11,12,13) must be displayed
Order (ID=103 with product IDs (10,12,14,15) must NOT be displayed

I hope i'm more clear now.

TIA again
Iordanis
From: --CELKO-- on 4 Mar 2010 11:37
Relational division with a remainder.
 |  Next  |  Last
Pages: 1 2
Prev: non-equal DRI: a possible new sql feature
Next: Change management - views that select data from tables in diff