3

Table Code:

Col1
----
A1
A2
A3
B1
B2
C1
D1
D2

(I have other columns as well)

I am trying to create every possible combination EXCLUDING itself (i.e COL1:A1 COL2:A1) EXCLUDING havING it again the reverse way (i.e A1 A2, A2,A1)... They are to be in separate columns and there are other columns included as well. I am a newbie, go easy on me :)

So far I have:

SELECT 
    a.Col1, a.[differentcolumn],
    b.Col1, b.[differentcolumn] 
FROM 
    [dbo].code a
CROSS JOIN 
    [dbo].code b
WHERE 
    a.[col1] != b.[col1]

This is almost it but it gives me:

A1    A2
A2    A1

I only want it one way (The first one). How do I do this?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
MM92
  • 33
  • 4

1 Answers1

2

I'm not completely clear on your requirement, but do you just need this?

SELECT 
    a.Col1, a.[differentcolumn],
    b.Col1, b.[differentcolumn] 
FROM 
    [dbo].code a 
       INNER JOIN [dbo].code b ON a.[col1] < b.[col1]

This will join the table to itself on col1, but using < means that you won't see the values where the left-hand copy has a col1 greater than or equal to the right-hand copy, which seems to be what you want.

Matt Gibson
  • 37,886
  • 9
  • 99
  • 128
  • Thanks, that did it, I didn't know you can use '<' on non-numeric fields. – MM92 Nov 03 '15 at 12:18
  • 1
    @MM92 Yup, just does an alphabetic comparison (well, I say "just"; the exact results depend on [the collation](https://technet.microsoft.com/en-us/library/aa174903%28v=sql.80%29.aspx?f=255&MSPPError=-2147217396), which can be important in some cases, e.g. whether "e" and "é" are counted as the same, but that's not important in this case :) ) – Matt Gibson Nov 03 '15 at 12:27