Select two columns for two different tables in SQL Server 2000.

Question

I have a database with two tables (Table1 and Table2). Table1 has one column ColumnA and Table2 has one column ColumnB i want to select both the columns,

looking for something like:

ColumnA in Table1:

a

b

c

ColumnA in Table2:

d

e

f

Result set should be:

a d

b e

c f

Thanks in advance..

Answer 1

Since you have no relation between the two tables, this operation is not really defined. What row in table1 goes with what row in table2?

You should set up a relation.

What is it you want to achieve anyway?

Answer 2

I am pretty sure sql server 2000 supports table vars so you can try this

DECLARE @TableA TABLE(
     ID INT IDENTITY(1,1),
     Val VARCHAR(50)
)

INSERT INTO @TableA (Val) SELECT ColumnA FROM Table1

DECLARE @TableB TABLE(
     ID INT IDENTITY(1,1),
     Val VARCHAR(50)
)

INSERT INTO @TableB (Val) SELECT ColumnB FROM Table2

SELECT a.Val,
     b.Val
FROM    @TableA a INNER JOIN
     @TableB b ON a.ID = b.ID

Answer 3

I don't know the big picture but from what you've said, here's an example. There has to be some way to define which record in table 1 should match up with a record in table 2. I'm assuming they match up on the ordering when ordered by the column in each table (e.g. record 1 from Table 1 ordered by column A, matches with record 1 from Table 2 ordered by column B). This example requires SQL 2005 or higher.

DECLARE @T1 TABLE (A varchar(10))
DECLARE @T2 TABLE (B varchar(10))

INSERT @T1 VALUES ('a')
INSERT @T1 VALUES ('b')
INSERT @T1 VALUES ('c')
INSERT @T2 VALUES ('d')
INSERT @T2 VALUES ('e')
INSERT @T2 VALUES ('f')

SELECT A, B
FROM
(
    SELECT ROW_NUMBER() OVER (ORDER BY A ASC) AS RowNo, A
    FROM @T1
) t1
JOIN
(
    SELECT ROW_NUMBER() OVER (ORDER BY B ASC) AS RowNo, B
    FROM @T2
) t2 ON t1.RowNo = t2.RowNo

Answer 4

How will the system know to associate the 'a' value from table1 with the 'd' value from table 2? If someone adds another row to table2 with value 'c' should your query now output

 null-  c
  a  -  d
  b  -  e
  c  -  f

or

  a  -  c
  b  -  d
  c  -  e

or

  a  -  c
  b  -  d
  c  -  e
 null-  f

??? --- You have to specify in some way what rules to use to associate the rows from table1 with the rows from table2.

If you just want the rows associated based on alphabetical sorting, then if the values are unique in each of the tables, (using standard SQL only), try this

   Select Z1.ColumnA, z2.ColumnB
   From  (Select ColumnA,
              (Select Count(*) 
               From Table1
               Where ColumnA < t1.ColumnA) RowNo,
          From Table1 T1) z1
    Join (Select ColumnB,
              (Select Count(*) 
               From Table2
               Where ColumnB < t2.ColumnB) RowNo,
          From Table2 T2) z2
       On z1.RowNo = z2.RowNo
   Order By z1.RowNo