SQL query question

Discussion in 'OT Technology' started by PPI Typhoon, Dec 15, 2003.

  1. PPI Typhoon

    PPI Typhoon Guest

    Ok, here's a project that I have. I'm wondering if anyone can give me insight if it's possible.

    Ok, I have two different tables. One contains a size code for an item we sell such as (along with measurements specific to that code, that are not important at this point) For information purposes, the table name is "size_data" and the value below is "size_code", which is the primary key in the table.

    THS-101
    THS-102
    THS-103
    ....
    ...
    .

    I have another table that contains that material data that they're using. This has a code as well of :

    700
    701
    702
    727
    728
    ...
    ..
    .

    That table is called "material_data" and the value is "material_code", which is the primary code in that table.


    Now, what I want to do is concatenate the values, but in every combination possible so that we can document every possible combination of the numbers.

    For an output example:

    THS-101-700
    THS-101-701
    ..
    ..
    .
    THS-102-700
    THS-102-701
    THS-102-702
    ..
    ..
    .


    Is a query like that even possible with SQL? I can't figure it out and I'm spinning my wheels.

    Anybody able to help me out on this one??

    Thanks, I appreciate it.

    Dan
     
  2. crotchfruit

    crotchfruit Guest

    i don't know how to do this with sql, but i would imagine that a non-sql method wouldn't be that hard. basically something like (pseudocode):

    Code:
    select size_code from size_table
    foreach (size_code)
    {
       select material_code from material_table
       foreach (material_code)
       {
           printf("%s-%s", size_code, material_code);
       }
    }
    
    of course there are plenty of possible optimizations, but that is the basic scheme..?
     
  3. sounds like you're trying to create a cartesian join? if so, it's very doable in SQL, but if you have more than just a few records, you're going to lock up your dbms and db server hardcore ...
     
  4. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    You normally get this resultset when you simply don't join the tables at all. Usually a nuisance, but this will do exactly what you want.
     
  5. this is definitely something to be done in scripted or programmatic nested loops, not via a resource-intensiveCartesian process on the part of the DBMS ...
     
  6. PPI Typhoon

    PPI Typhoon Guest

    Yes, it is a cartesian joining, which does work well. As far as locking it up, it takes about 15 seconds to run on my machine that returns 527,328 different combinations

    Here's what I have so far. I need to clean it up, but you get the idea. I just need to different conditions in a better way than just repeating with different limitations.

    /*
    Inventory query for Thermal and Self-Laminating labels
    All possible combinations
    Author: Dan *******
    Date: 12/16/03
    */
    select (sz.lbl_size_code +
    '-' + mc.material_code +
    '-' + cl.lbl_color_code +
    cz.core_size_code +
    oc.orient_code +
    pp.pre_print_code +
    '-' + c.custom_code)as InventoryCode,
    lbl_size_code as SizeCode,
    material_code as Material,
    lbl_color as Color,
    core_size as CoreSize,
    pre_print_desc as PrePrintDescription,
    custom_desc as CustomDescription
    into ##inventory /* Global Temporary Table */
    from label_size sz
    CROSS JOIN material_data mc
    CROSS JOIN label_color cl
    CROSS JOIN core_size cz
    CROSS JOIN orientation oc
    CROSS JOIN pre_print pp
    CROSS JOIN custom c
    where mc.material_code IN (500, 501, 502, 700, 701, 702, 703, 704, 705, 706, 709, 710, 715, 716, 723, 790)
    and lbl_type = 'THL' /* Thermal Labels Only */
    and cl.lbl_color_code <> 'CR' /* No Chrome */
    and cl.lbl_color_code <> 'CL' /* No Clear */
    and cl.lbl_color_code <> 'AM' /* No Amber */
    and cl.lbl_color_code <> 'BF' /* No Buff */
    UNION
    select (sz.lbl_size_code +
    '-' + mc.material_code +
    '-' + cl.lbl_color_code +
    cz.core_size_code +
    oc.orient_code +
    pp.pre_print_code +
    '-' + c.custom_code)as InventoryCode,
    lbl_size_code as SizeCode,
    material_code as Material,
    lbl_color as Color,
    core_size as CoreSize,
    pre_print_desc as PrePrintDescription,
    custom_desc as CustomDescription
    from label_size sz
    CROSS JOIN material_data mc
    CROSS JOIN label_color cl
    CROSS JOIN core_size cz
    CROSS JOIN orientation oc
    CROSS JOIN pre_print pp
    CROSS JOIN custom c
    where mc.material_code IN (730, 735, 736)
    and cl.lbl_color_code = 'CR' /* No Chrome */
    and lbl_type = 'THL' /* Thermal Labels Only */
    UNION
    select (sz.lbl_size_code +
    '-' + mc.material_code +
    '-' + cl.lbl_color_code +
    cz.core_size_code +
    oc.orient_code +
    pp.pre_print_code +
    '-' + c.custom_code)as InventoryCode,
    lbl_size_code as SizeCode,
    material_code as Material,
    lbl_color as Color,
    core_size as CoreSize,
    pre_print_desc as PrePrintDescription,
    custom_desc as CustomDescription
    from label_size sz
    CROSS JOIN material_data mc
    CROSS JOIN label_color cl
    CROSS JOIN core_size cz
    CROSS JOIN orientation oc
    CROSS JOIN pre_print pp
    CROSS JOIN custom c
    where mc.material_code IN (402, 403, 407, 408, 409, 410)
    and cl.lbl_color_code = 'WH'
    and lbl_type = 'THL' /* Thermal Labels Only */
    UNION
    select (sz.lbl_size_code +
    '-' + mc.material_code +
    '-' + cl.lbl_color_code +
    cz.core_size_code +
    oc.orient_code +
    pp.pre_print_code +
    '-' + c.custom_code)as InventoryCode,
    lbl_size_code as SizeCode,
    material_code as Material,
    lbl_color as Color,
    core_size as CoreSize,
    pre_print_desc as PrePrintDescription,
    custom_desc as CustomDescription
    from label_size sz
    CROSS JOIN material_data mc
    CROSS JOIN label_color cl
    CROSS JOIN core_size cz
    CROSS JOIN orientation oc
    CROSS JOIN pre_print pp
    CROSS JOIN custom c
    where mc.material_code = '401'
    and cl.lbl_color_code = 'AM'
    and lbl_type = 'THL' /* Thermal Labels Only */
    UNION
    select (sz.lbl_size_code +
    '-' + mc.material_code +
    '-' + cl.lbl_color_code +
    cz.core_size_code +
    oc.orient_code +
    pp.pre_print_code +
    '-' + c.custom_code)as InventoryCode,
    lbl_size_code as SizeCode,
    material_code as Material,
    lbl_color as Color,
    core_size as CoreSize,
    pre_print_desc as PrePrintDescription,
    custom_desc as CustomDescription
    from label_size sz
    CROSS JOIN material_data mc
    CROSS JOIN label_color cl
    CROSS JOIN core_size cz
    CROSS JOIN orientation oc
    CROSS JOIN pre_print pp
    CROSS JOIN custom c
    where mc.material_code = '404'
    and cl.lbl_color_code = 'BF'
    and lbl_type = 'THL' /* Thermal Labels Only */
    UNION
    select (sz.lbl_size_code +
    '-' + mc.material_code +
    '-' + cl.lbl_color_code +
    cz.core_size_code +
    oc.orient_code +
    pp.pre_print_code +
    '-' + c.custom_code)as InventoryCode,
    lbl_size_code as SizeCode,
    material_code as Material,
    lbl_color as Color,
    core_size as CoreSize,
    pre_print_desc as PrePrintDescription,
    custom_desc as CustomDescription
    from label_size sz
    CROSS JOIN material_data mc
    CROSS JOIN label_color cl
    CROSS JOIN core_size cz
    CROSS JOIN orientation oc
    CROSS JOIN pre_print pp
    CROSS JOIN custom c
    where mc.material_code IN (727, 728, 729)
    and lbl_type = 'THS' /* Self-Laminating Labels Only */
    and cl.lbl_color_code <> 'CR'
    and cl.lbl_color_code <> 'AM'
    and cl.lbl_color_code <> 'BF'

    ORDER BY InventoryCode

    select * from ##inventory

    drop table ##inventory
     
  7. PPI Typhoon

    PPI Typhoon Guest

    Oh, and as you can see, I am just putting it into a global temporary table right now, but I need to dump it into a final database when done
     
  8. PPI Typhoon

    PPI Typhoon Guest

    And, as you can tell, I don't do this for a living, and I understand that my code is messy as hell. We are too small to have a DBA so I get to adopt the role.... :o
     

Share This Page