Member Avatar for sebaz

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
  • 112 Views
  • 6 Hours Discussion Span
  • Latest Post Latest Post by sknake
Member Avatar for sknake 
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
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.