#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2003
    Posts
    2
    Rep Power
    0

    SQL query brainfade


    Hi everyone. New guy here.

    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.

    I just want to take the first entry of one table, concatenate it to all the combinations of the second table.........go the next entry of the first table and concatenate it to all the combinations of the second table, and so on.

    Anybody able to help me out on this one??

    Thanks, I appreciate it.

    Dan
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2003
    Location
    Cincinnati, OH USA
    Posts
    111
    Rep Power
    12
    Try something like this: (not sure if it will work or not)

    SELECT (s.size_code + '-' + m.material_code) as SizeMaterial
    FROM size_data s
    CROSS JOIN material_data m


    See if that works...

    Jill
  4. #3
  5. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2003
    Posts
    2
    Rep Power
    0
    Thank you, that worked perfectly. Now I just need to keep applying that to 8 more designators........

    Thanks again, it is greatly appreciated.

    Dan

IMN logo majestic logo threadwatch logo seochat tools logo