Post reply

All Combinations

  • July 19, 2006 at 2:15 am

    #173387

    Hi

    Got a question, I have the following table

    Category           Group        Block

    Welsh              Group1            A    

    Welsh              Group2             C   

    Fishing             Group1             B   

    Fishing            Group2            C   

    Snooker            Group1           A   

    Snooker             Group1           B   

    What I need is a query that will return every possible combination of the 3 categories (Welsh, Fishing, Snooker)

    Example:

    A combination would be

    Welsh     Group1   A  

    Fishing    Group1   B 

    Snooker  Group1   A   

    At the moment the data is in 2 tables

    table 1 = categories (Welsh, Fishing, Snooker)

    table 2 = groups / block (Group1 , A)

    Joined as follows

    SELECT Category, Group, Block

    FROM Table1 INNER JOIN Table2 ON Table1.Categoryid = Table2.CategoryId

    I've looked into using WITH CUBE Grouping, but i can't get the results i need, any ideas ?

    Thanks in Advance

    DAve

     

     

  • July 19, 2006 at 2:37 am

    #650030

    Another possibility is that you create a cartesian product (CROSS JOIN) between the two tables.

    Something like

    SELECT      Categories.Category,

                Groups.Group,

                Groups.Block

    FROM        Categories

    CROSS JOIN  Groups

    OR

    SELECT      DISTINCT Categories.Category,

                Groups.Group,

                Groups.Block

    FROM        Categories

    CROSS JOIN  Groups

    N 56°04'39.16"
    E 12°55'05.25"

  • July 19, 2006 at 3:07 am

    #650033

    Cross join is certainly the way to achieve the described result. But you mention every possible combination of all three categories - do you want to ignore the association between group and block, and return all combs. of group/block? If so you will need a second cross join to those values. If the block ids form a unique key in some other table, you should use that table.

    Tim Wilkinson

    "If it doesn't work in practice, you're using the wrong theory"
    - Immanuel Kant

  • July 19, 2006 at 3:39 am

    #650040

    Thanks for all the help, i'll give the cross join a go.  Sounds like it'll do the job.

    All the best

    Dave

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply