Simple query but I am drawing a blank today.

Discussion in 'General Web Coding' started by mack5511, Jun 17, 2008.

  1. mack5511

    mack5511 Guppy

    Hello,

    I am sure this is a fairly simple query but I am drawing a blank today. I have a single table of 4500 rows that contains the following fields.

    Table Name = RK_BOM

    Model_Number
    Model_Description
    Item_Number
    Option_Class
    Country

    Background on table data: The table contains a list of models from different countries. The models are composed of one or more items to form a kit. The idea is to compare all the items and see how many of the kits contain similar items.

    From the info above I am trying to write a query to look at all the Item_Numbers and match them to all the applicable Model_Number(Kits).

    Any help would be appreciated.

    Thanks,

    Mack
  2. Penhall

    Penhall Perch

    I may be missing something here but is it not just a

    select Item_Number, Model_Number from RK_BOM GROUP BY Model_Number

    ?
  3. mack5511

    mack5511 Guppy

    Penhall,

    It may be my attempt at explaining is off. That query errors out as Item_Number is not part of the group by clause. I will try to give a better explanation.

    The Item_number field contains the global items list for all countries models of kits. The person who is requesting this wants to see how many kits throughout the world contain similar items. If 2 kits contain the exact matches she wants to know. But also if those same 2 kits have another kit that has 3 of the 4 items for example she wants to see that too. I am thinking I need to count the times a certain Item number shows up in the list and determine the matching off that number. If an item_number exists only once it is not in any other kits. I am trying to work through the logic as we speak. I don't think it is as simple as a group by query. I want to thank you for answering. I do appreciate you help.


    Thanks,

    Mack
  4. Penhall

    Penhall Perch

    This is a bit confusing to grasp your table data (it looks like it a flat table rather than a relational database)

    Is
    a) Model_Number is a unique kit number
    and
    b) Item_Number is actually a LIST of parts

    EG:
    Model1, (part 1, part 2, part3 etc)

    or does each Item_Number exist in a seperate record.

    Model1,part1
    Model1,part2
  5. mack5511

    mack5511 Guppy

    Penhall,

    The data structure is like the second example you have. So my data looks like this.

    Model_1, Model Description_1, Item_Number_1, Country_1
    Model_1, Model Description_1, Item_Number_2, Country_1
    Model_1, Model Description_1, Item_Number_3, Country_1
    Model_1, Model Description_1, Item_Number_4, Country_1
    Model_1, Model Description_1, Item_Number_5, Country_1
    Model_1, Model Description_1, Item_Number_6, Country_1
    Model_1, Model Description_1, Item_Number_7, Country_1


    Model_2, Model Description_2, Item_Number_1, Country_2
    Model_2, Model Description_2, Item_Number_2, Country_2
    Model_2, Model Description_2, Item_Number_3, Country_2
    Model_2, Model Description_2, Item_Number_4, Country_2
    Model_2, Model Description_2, Item_Number_5, Country_2
    Model_2, Model Description_2, Item_Number_6, Country_2

    etc


    Models can have as little as a single part or many. The Item_numbers are unique to the item but they can make up any kit worldwide. We have 14 countries. This is an attempt to simplify the Bill of Materials across the company. So all the models with a similar parts list can be identified.

    I am sorry if this seems obtuse I have been called upon to try and help out someone else and am still working through everything also.


    Thanks,

    Mack
  6. Penhall

    Penhall Perch

    select Item_Number, Model_Number from RK_BOM GROUP BY Item_Number

    Would return something ike

    Item_Number_1, Model_Number1
    Item_Number_1, Model_Number2
    Item_Number_1, Model_Number3
    Item_Number_2, Model_Number1
    Item_Number_3, Model_Number1
    Item_Number_3, Model_Number2
    Item_Number_3, Model_Number3

    or simply

    select Item_Number,Model_Number from RK_BOM order by Item_Number (asc or desc)

    to get a list of Item_Numbers with corresponding model_numbers

    BUT

    Do you just want Item_Numbers that appear multiple times.

    - again, just trying to think this through with you.
  7. mack5511

    mack5511 Guppy

    Penhall,

    From my thinking the only item numbers that would appear more than once would mean they are part of multiple models.So if that was the case I could eliminate the single items from the list and that would leave only a subset of the models that have items in common. Then a group by might work better ultimately I will be exporting the results to an Excel sheet so I can do some sorting and grouping on that end if I have the relevant data. I can put all the unmatched items on one worksheet and the matched on a separate worksheet. I am not sure if this helps or not.


    Thanks,

    Jim

    P.S. I will sleep on it and attack it fresh in the morning. This was dumped in my lap with a hurry hurry hurry can you help me. Needless to say it was not part of my work before today.
  8. Penhall

    Penhall Perch

    select Item_Number, Model_Number
    from RK_BOM
    GROUP BY Item_Number
    Having Count(Item_Number)>1

    and

    select Item_Number, Model_Number
    from RK_BOM
    GROUP BY Item_Number
    Having Count(Item_Number)=1
  9. mack5511

    mack5511 Guppy

    Penhall,

    After some sleep and a discussion with some others it was not as simple as I had thought. I needed to compare every kits contents against every other kit in the list and then cross reference the parts to give a display of one kits contents and the list of other kits in common. Lucky for me we have a guy that knew exactly what was needed and he took over. I just wanted to thank you again for your help.



    Thanks,

    Jim

Share This Page

JodoHost - 26,000 hosting end-users in 100 countries
Plesk Web Hosting
VPS Hosting
H-Sphere Web Hosting
Other Services