querry problem

Expand Post »

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

Similar Threads

Calculator Missing - help please in Windows NT / 2000 / XP
SQL querry Combo Box Question in C#
Intel mainboard D845HV, front panel configuration in Motherboards, CPUs and RAM
accessing an empty querry result in PHP
Accesing tables rows in Stored procedure without using cursors in MS SQL

sebaz

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

1 posts
since Jul 2009

Permalink

Jul 1st, 2009

Re: querry problem

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:

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

sknake

Featured Poster

Reputation Points: 1749

Solved Threads: 735

Senior Poster

Offline

3,948 posts
since Feb 2009

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in MS SQL Forum Timeline: Table Chaos

Next Thread in MS SQL Forum Timeline: Code inside CATCH not execute

DaniWeb Message

Cancel Changes

User Name
Password