Combing Tables With 'Union' Statement Leave A Column

[lhooker]

I inadvertently deleted my previous thread. Sargeant reply to my previous thread recommending using the 'Union' statement. I did and below are the results. Below is what I used. Please advise. Thanks again ! ! !

SELECT Table1.Column1, Table1.Column2, ""
FROM Table1
UNION SELECT Table2.Column1, "", Table2.Column3
FROM Table2;


Results

NewTable
Column1 Column2 Column3
A...............................3.1.......
A...............................3.2.......
A...............................3.3.......
A...............................3.4.......
A...............2.1.......................
A...............2.2.......................
A...............2.3.......................
A...............2.4.......................

 

[FoFa]

Um, OK, so what is wrong with that?

 

[lhooker]

FoFa,

The rows with '3.1', '3.2', '3.3', and '3.4' all have a blank field in column2. I would like to eliminate the blank field in column2.

 

[pbaldy]

Each section of a union query must have the same number of columns, so try:

SELECT Table1.Column1, Table1.Column2
FROM Table1
UNION SELECT Table2.Column1, Table2.Column3
FROM Table2;

 

[lhooker]

Paul,

Thanks for replying, your solution eliminate the third column. I need the three columns, but I don't want the blank fields. Thanks again for replying ! ! !

 

[pbaldy]

What do you want in the second column then?

 

[Ryko]

I think I read lhooker's previous thread which was deleted

I believe he has two tables like:

Table1
Column1 Column2 Column3
A 2.1 (blank)
A 2.2 (blank)
etc.

Table2
Column1 Column2 Column3
A (blank) 3.1
A (blank) 3.2
etc.

He desires result like:

Table3
Column1 Column2 column3
A 2.1 3.1
A 2.2 3.2
etc.

 

[neileg]

I think we just need a simple join here since it looks like either column 2 or column 3 is always blank. However, it's not clear how the records in table 1 and 2 are related so as to join the correct records together.

 

[lhooker]

Neil,

Column1 will have the same identify value in both tables. Column2 or column3 will vary with the blank fileds in the column. Thanks ! ! !

 

[neileg]

So the fact that field 1 is always A in your example is misleading? Column A will actually be a range of unique values?

 

[lhooker]

Neil,

No, Column1 will always be the same in both tables. Table1 (column2) will always be blank and table2 (column3) will also be be blank. I want to move the data in table2 (column2) to table1 (column2). This would give a merged table (with table1 and table2 data) with no blank coluns.

 

[Sergeant]

You could just alias the fields in each query like this:

SELECT Table1.Column1, Table1.Column3 as C2
FROM Table1
UNION SELECT Table2.Column1, Table2.Column2 as C2
FROM Table2;

 

[lhooker]

Sergeant,

Your query eliminated a column, but I need three columns. The first column would have 'A's, the second column would have '2.1', '2.2', and '2.3', and the third column would have '4.1', '4.2', and '4.3'.

 

[neileg]

Neil,

No, Column1 will always be the same in both tables. Table1 (column2) will always be blank and table2 (column3) will also be be blank. I want to move the data in table2 (column2) to table1 (column2). This would give a merged table (with table1 and table2 data) with no blank coluns.

How do we know that Row1 in Table1 corresponds with Row1 in Table2. We have to have a field to join the two sets of data on. Is there a PK? An autonumber perhaps?

Tables in a database are basically unordered datasets. You have to have a field that determines the order.

 

[lhooker]

Neil,

The data in column one will always be the same. There is no primary key. I'm coming to the conclusion that this cannot be done. It would be nice if there was a function like the 'INSERT INTO' statment, but into a field, rather than a row. Thanks for trying ! ! !

 

[FoFa]

Sergeant,

Your query eliminated a column, but I need three columns. The first column would have 'A's, the second column would have '2.1', '2.2', and '2.3', and the third column would have '4.1', '4.2', and '4.3'.

How do you know to match up the 2.1 with the 4.1?
What you want is possible, but the SQL has to know how to do the matching