Common variants list

[in2view]

Hi,

I have table #1 "Items" with "Item No" as the Primary Key.
I also have linked table #2 "Item Variants" with foreign Key "Item Number" and Primary Key "Variant Code" (alpha-numeric).

Each Item nay have multiple Variants, but only one combination of Item/Variant Code per the Item Variant Table.

I have three Items A, B, and C.
Each Item has multiple Variants.

Item A Variants = "Red, White, Blue"
Item B Variants = "Red, Blue, Black, Yellow, White"
Item C Variants = " Red White, Blue, Purple"

I would like to select a distinct list of all Variants from the Variant Table where Common Variants exist (i.e. Red, White, and Blue).

For example I want to create a list for a drop-down box that has only the COMMON Variants of the three Items.

I would like to do this using just one SQL statement without having to do loops, etc.

Any ideas?

All input appreciated.
Thanks for reading.

Dave

 

[SQLBill]

Take a look at FAQ183-1151 which gives some ideas about working with comma delimited strings. Once you parse out the information from the strings you can use GROUP BY and HAVING to get the duplicates.

Refer to Books OnLIne for GROUP BY and HAVING.

-SQLBill

 

[in2view]

Sorry for the confusion, but the Variants are actually in a field in the table in seperate lines, AND NOT AS COMMA SEPERATED STRINGS.

VARIANT CODE TABLE
ITEM_CODE VARIANT_CODE
ITEM_A RED
ITEM_A WHITE
ITEM_A BLUE
ITEM_B RED
ITEM_B BLUE
ITEM_B BLACK
ITEM_B YELLOW
ITEM_B WHITE
ITEM_C RED
ITEM_C WHITE
ITEM_C BLUE
ITEM_C PURPLE

The Item Table has ITEM_CODE as the Primary key and a one-to-many relationship with the Item_Variant table.

The Item Varaint Table has a dual key of Item_Code & Variant_Code as primary with Item_Code.

I have a file for Bill of materials that has a list if Items and quantities that make a sub-assembly.

For example this list has Items A, B, & C as components.

I want to compare the Varaints of Items A, B, and C and link the list of COMMON Variant choices to the sub-assembly Variant list, so when I populate the individual Item Variant choices after selecting it on the sub-assembly, whatever Variant I choose is among the list for each Item.

I hope that explains it a little better.

 

[SQLBill]

Something like:

SELECT DISTINCT Variant_Code
FROM tablename
GROUP BY Variant_Code
HAVING COUNT(Variant_Code) > 1

-SQLBill

 

[in2view]

Something like that, except that its only for for

Items A, B, and C that may occur in the BOM list more than once:

i.e. BOM Item List for a Dining Room Table consists of:

4 of Item_A (legs)
1 of Item_B (table top)
4 of Item_D (chairs)
that each consist of 4 of Item_C (chair legs)
etc.

When exploded the list now becomes
4 of Item_A (legs)
1 of Item_B (table top)
4 of Item_c (chair legs)
4 of Item_C (chair legs)
4 of Item_C (chair legs)
4 of Item_C (chair legs)


So can I nest one SQL statement in another by saying something to the effect of...

SELECT DISTINCT Variant_Code
FROM tablename
WHERE (SQL statement selecting distinct items in list above)
GROUP BY Variant_Code
HAVING COUNT(Variant_Code) > 2 (Since 3 items, the Distinct Variant needs to occur 3 times?)

Keeping in mind that I have three Distinct Item Codes to search for counting purposes to find each Variant occuring more than twice.

Confused yet?