representative sub-data

Since indexed views aren't going to work for me, I'm working on
revamping a large, ugly query that hits a large, ugly database.
The database stores inventory.
Some items in inventory are single items (e.g. a calendar), while
others are grouped (e.g. a shirt with several color/size options).
The schema of the relevant tables looks like:
product:
--
ID
sku
Color
Description
Price
productGroup:
--
ID
sku
Name
productGroupElement:
--
ID
productID
productGroupID
I need a query that returns both the standalone and grouped items
together. I'm doing this with a union. The tricky part (for me) is in
getting the grouped items, grabbing representative data from one of the
standalone items that comprise the group.
Here's the current query I'm using:
select 'standalone' as flag, id, sku, color, description
from product
where id NOT IN (select productID from productGroupElement)
UNION
select 'grouped' as flag, pg.id,pg.sku,p.color,p.description
from product p inner join productGroupElement pge on p.id = pge.productID
INNER JOIN productGroup pg on pg.id = pge.productGroupID
where p.id = (
select max(pge1.ItemID)
FROM productGroupElement pge1
INNER JOIN product p1 ON
pge1.productID = p1.id
WHERE pge1.productGroupID = pge.productGroupID
)
So what I'd like to know is:
1) Is the union approach a reasonably efficient way to combine
standalone and grouped items?
2) is the sub query in the WHERE clause of the second query the best
way to grab representative data?
Thanks,
ErikHI Erik
"voldengen@.gmail.com" wrote:
> Since indexed views aren't going to work for me, I'm working on
> revamping a large, ugly query that hits a large, ugly database.
> The database stores inventory.
> Some items in inventory are single items (e.g. a calendar), while
> others are grouped (e.g. a shirt with several color/size options).
> The schema of the relevant tables looks like:
> product:
> --
> ID
> sku
> Color
> Description
> Price
>
> productGroup:
> --
> ID
> sku
> Name
>
> productGroupElement:
> --
> ID
> productID
> productGroupID
>
You should check out http://www.aspfaq.com/etiquette.asp?id=5006 on how to
post useful DDL and sample data
> I need a query that returns both the standalone and grouped items
> together. I'm doing this with a union. The tricky part (for me) is in
> getting the grouped items, grabbing representative data from one of the
> standalone items that comprise the group.
> Here's the current query I'm using:
> select 'standalone' as flag, id, sku, color, description
> from product
> where id NOT IN (select productID from productGroupElement)
> UNION
> select 'grouped' as flag, pg.id,pg.sku,p.color,p.description
> from product p inner join productGroupElement pge on p.id => pge.productID
> INNER JOIN productGroup pg on pg.id = pge.productGroupID
> where p.id = (
> select max(pge1.ItemID)
> FROM productGroupElement pge1
> INNER JOIN product p1 ON
> pge1.productID = p1.id
> WHERE pge1.productGroupID => pge.productGroupID
> )
>
What happens to the items returned by:
select 'others' as flag, pg.id,pg.sku,p.color,p.description
from product p inner join productGroupElement pge on p.id =pge.productID
INNER JOIN productGroup pg on pg.id = pge.productGroupID
where p.id <> (
select max(pge1.ItemID)
FROM productGroupElement pge1
INNER JOIN product p1 ON pge1.productID = p1.id
WHERE pge1.productGroupID = pge.productGroupID
)
> Thanks,
> Erik
Would this give what you want?
SELECT CASE WHEN g.productID IS NULL THEN 'standalone' ELSE 'grouped' END AS
flag, p.id, p.sku, p.color, p.description
from product p
LEFT JOIN productGroupElement g ON p.id = g.productID
John|||On 29 Dec 2006 11:59:13 -0800, voldengen@.gmail.com wrote:
(snip)
>1) Is the union approach a reasonably efficient way to combine
>standalone and grouped items?
Hi Erik,
Since the constants in the SELECT lists makes the two sides of the UNION
disjunct, you should change it to UNION ALL. Without the ALL, SQL Server
will do extra work to find and remove duplicates.
>2) is the sub query in the WHERE clause of the second query the best
>way to grab representative data?
Only if a sample that is achieved by always using the highest ItemID can
be considered representative. Most statisticians would disagree. (Would
you trust a political popularity poll that is done by interviewing the
oldest member of each household?)
--
Hugo Kornelis, SQL Server MVP