MSSQL Query Help

[jondel81]

Having problem completing this query. I have a list of items. Some items need to be grouped by a list, some by a range. I was thinking of useing two tables, one for the items, and one for the groups. The groups would have something like groupid, title, listnumbers, rangelow, and rangehigh. The tables are in sql database. For example:
The list of items numbers are 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20. In the group table, it would be listed like this:
G1, Group1, 1,,
G1, Group1, 6,,
G2, Group2, ,2,5
G3, Group3,6,18,20
G3, Group3,7,15,17
G3, Group3,8,14,16
G3, Group3,9,11,13

In this example, Group1 is a list, group2 is a range, and group3 is a list of ranges. I can make a query that pulls all the items just in the groups:
SELECT ECC_ITEMS.NBR, Group.Group_Name, Group.Title, ECC_ITEMS.DESCR, ECC_ITEMS.REG_PRC
FROM Group, ECC_ITEMS
WHERE ECC_ITEMS.NBR Between Group.RangeLow And Group.RangeHigh Or ECC_ITEMS.NBR=Group.GroupItems
Now, I am not sure how to put the rest of the items (the ones that aren't in a group) in that query. I was thinking on making a union and the second query being a unmatched query. Not sure how to make it were that query is "unmatched" with a table in the same query. And ideas on how to make the second part of the union query?

------

http://www.carealotpets.com

 

[jondel81]

Here is what I have so far in terms of querys. I would like to combind them and make a DTS.

ECC_ITEMS:
SELECT ITEM.NBR, ITEM.DESCR, ITEM.DESC_LIN_1, ITEM.DESC_LIN_2, ITEM.TYP, ITEM.USR_DEF_1, ITEM.CAT, ITEM.SUB_CAT, ITEM.USR_DEF_4, ITEM.ECOMMERCE_FLG, ITEM.TXBL_FLG, ITEM.TAX_COD, ITEM.WEIGHT, ITEM.STAT, ITEM.REG_PRC, ITEM.PRC_UNIT
FROM ITEM
WHERE (((ITEM.USR_DEF_4)="SU04" Or (ITEM.USR_DEF_4)="FA04"));

GroupandRange:
SELECT ECC_ITEMS.NBR, Group.Group_Name, Group.Title, ECC_ITEMS.DESCR, ECC_ITEMS.REG_PRC
FROM [Group], ECC_ITEMS
WHERE (((ECC_ITEMS.NBR) Between Group.RangeLow And Group.RangeHigh Or (ECC_ITEMS.NBR)=Group.GroupItems));

SingleItems:
SELECT ECC_ITEMS.NBR, 0 AS Sing, ECC_ITEMS.DESCR, ECC_ITEMS.DESC_LIN_1, ECC_ITEMS.REG_PRC
FROM ECC_ITEMS LEFT JOIN GroupandRange ON ECC_ITEMS.NBR = GroupandRange.NBR
WHERE (((GroupandRange.NBR) Is Null));

UnionQuery:
SELECT GroupandRange.Group_Name, GroupandRange.Title, Count(GroupandRange.DESCR) AS CountOfDESCR, Min(GroupandRange.REG_PRC) AS MinOfREG_PRC, Max(GroupandRange.REG_PRC) AS MaxOfREG_PRC
FROM GroupandRange
GROUP BY GroupandRange.Group_Name, GroupandRange.Title
union
SELECT SingleItems.NBR, SingleItems.DESCR, Count(SingleItems.Sing) As CountOf, Min(SingleItems.REG_PRC) as MinOfReg_Prc, Max(SingleItems.REG_PRC) as MaxOfReg_PRC
FROM SingleItems
Group By SingleItems.NBR, SingleItems.DESCR;

It looks like alot, but I think it can be done, just not sure how. Thanks for looking into this.

------

http://www.carealotpets.com

 

[jondel81]

This is what I came up with. Works okay.

SELECT ItemGroups.Title, ItemGroups.Group_Name, ITEM.NBR, ITEM.DESCR, ITEM.TYP, ITEM.USR_DEF_1, ITEM.CAT, ITEM.SUB_CAT, ITEM.USR_DEF_4,
ITEM.ECOMMERCE_FLG, ITEM.TXBL_FLG, ITEM.TAX_COD, ITEM.WEIGHT, ITEM.STAT, ITEM.REG_PRC, ITEM.PRC_UNIT
FROM ITEM CROSS JOIN
ItemGroups
WHERE (ITEM.NBR BETWEEN ItemGroups.RangeLow AND ItemGroups.RangeHigh) OR
(ITEM.NBR = ItemGroups.GroupItems)
union
SELECT DESCR as Title, NBR as GroupName, NBR, DESCR, TYP, USR_DEF_1, CAT, SUB_CAT, USR_DEF_4, ECOMMERCE_FLG, TXBL_FLG,
TAX_COD, WEIGHT, STAT, REG_PRC, PRC_UNIT
FROM ITEM
WHERE (NOT (NBR IN
(SELECT ITEM.NBR
FROM ITEM CROSS JOIN
ItemGroups
WHERE (ITEM.NBR BETWEEN ItemGroups.RangeLow AND ItemGroups.RangeHigh) OR
(ITEM.NBR = ItemGroups.GroupItems))))

------

http://www.carealotpets.com