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,
Erik
HI 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