0

I'd like to make a querry prom 2 tables that have the same information. Tables have only one column named "fish". I'd like to create a querry that will show me every combination from first table with the second table (in 2 separate columns). For example:
My table contains: A,B,C.
I'd like to get in the querry the following info:
column1 column2
A A
A B
A C
B A
B B
B C
C A
C B
C C

it would be great if in the querry i could eliminate the AA,BB and CC :). Thanks in advance

2
Contributors
1
Reply
2
Views
8 Years
Discussion Span
Last Post by sknake
0
IF OBJECT_ID('Table1', 'U') IS NOT NULL DROP TABLE Table1
Create Table Table1
(
  Fish varchar(1)
)

IF OBJECT_ID('Table2', 'U') IS NOT NULL DROP TABLE Table2
Create Table Table2
(
  Fish varchar(1)
)

GO

Insert Into Table1 (Fish) Values ('A')
Insert Into Table1 (Fish) Values ('B')
Insert Into Table1 (Fish) Values ('C')

Insert Into Table2 (Fish) Values ('A')
Insert Into Table2 (Fish) Values ('B')
Insert Into Table2 (Fish) Values ('C')


GO

Select *
From Table1,Table2
Order By Table1.Fish, Table2.Fish

Results:

(1 row(s) affected)
Fish Fish
---- ----
A    A
A    B
A    C
B    A
B    B
B    C
C    A
C    B
C    C
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.