MS SQL

MS SQL RSS

querry problem

Please support our MS SQL advertiser: Intel Parallel Studio Home

•

Join Date: Jul 2009

Posts: 1

Reputation: sebaz is an unknown quantity at this point

Solved Threads: 0

sebaz

Offline

Newbie Poster

querry problem

Jul 1st, 2009

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

Last edited by sebaz; Jul 1st, 2009 at 11:59 am. Reason: bad title

•

Join Date: Feb 2009

Posts: 3,242

Reputation: sknake has much to be proud of

Solved Threads: 577

sknake

Offline

.NET Enthusiast

Re: querry problem

Jul 1st, 2009

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
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
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:

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
(1 row(s) affected)
Fish Fish
---- ----
A    A
A    B
A    C
B    A
B    B
B    C
C    A
C    B
C    C
(1 row(s) affected)
Fish Fish
---- ----
A    A
A    B
A    C
B    A
B    B
B    C
C    A
C    B
C    C