-4

I have similar datasets to the following

TABLE1
Column1,Column2,Column3,
ID1    ,ID2    ,Value1

TABLE2
Column1,Column2,Column3,
ID3    ,ID2    ,Value2

I would like to get

TABLE3
Column1,Column2,Column3
ID1    ,ID2    ,Value1
ID3    ,ID2    ,Value2

I am currently using the following code

select * from table1
union all
select * from table2;

This is successfully adding the columns together, but I am getting some erroneous values. I am not sure what else I should be doing, any help is appreciated! I am using Vertica SQL.

EDIT ****** By erroneous values I mean for example: table2.value2 = (null) table3.value2 = 594792

Thanks

Peter Gibbs
  • 25
  • 9
  • 4
    what do you mean by erroneous values? you should show some sample data so we can understand. – Vamsi Prabhala Jul 21 '17 at 19:37
  • 1
    Can you put the error in here? It's also possible that all your columns are not matching. I know you only have 3 columns in your example but possibly in your real tables, one have 10 columns, the other have 13 or they both have 10 but different names. – Isaiah3015 Jul 21 '17 at 19:54
  • 1
    `UNION ALL` will include duplicates in the result set, whereas `UNION` will not. That tidbit aside, your query will provide your expected results. – Aaron Dietz Jul 21 '17 at 19:54
  • Hi everyone, By erroneous values I mean table2.value2 is not equal to table3.value2 For example table2.value2 = (null) while table3.value2 = 530492 I'm fairly certain that the column names are the same. There is a unique key between the two datasets, but of course get duplicate field errors when attempting to join normally. I do notice that not all values in table 3 are incorrect. – Peter Gibbs Jul 21 '17 at 20:03
  • @PeterGibbs: UNION ALL just concatenates rows from the two tables into one result table - there's no comparison, join, or anything between the values from the two tables. – 500 - Internal Server Error Jul 21 '17 at 20:06
  • @500 - Internal Server Error This is exactly what I want to happen, just to have the tables stuck together. If I did not get these errors then I would be happy :) – Peter Gibbs Jul 21 '17 at 20:08
  • @isaiah3015 there isn't an actual error, I'm just getting unexpected values – Peter Gibbs Jul 21 '17 at 20:11
  • @OP - UNION just gives you all the rows in both tables. Think of it as 2 query being executed at the same time and displaying you the results. If table2.value2 = (null) while table3.value2 = 594792 then you get one row with NULL and the other row with 594792. What do you believe is the error? – Isaiah3015 Jul 21 '17 at 20:15
  • @PeterGibbs if you're getting unexpected values, then these values are in your tables, clean up them first – Iłya Bursov Jul 21 '17 at 20:16
  • @OP, in the end, if you don't want any NULL values in your result for column2 what you need is a JOIN where the table2 IS NOT NULL. This is the wrong function to use. – Isaiah3015 Jul 21 '17 at 20:17
  • @Isaiah3015 but table 3 is the resultant table. I have no idea where the 594792 comes from. This value seems to be created as a result of the union all. – Peter Gibbs Jul 21 '17 at 20:21
  • @OP, without actual sample of your database and actual data that we can see and your actual expected result, it will be very difficult for any of us to help you. I suggest you edit your question with actual 2 tables with real data in it (just make it up if its sensitive). Then, an example of what your expected result should be. – Isaiah3015 Jul 21 '17 at 20:22

2 Answers2

0

Have you tried using UNION instead of UNION ALL?

UNION will remove duplicate data, which I'm wondering if that's your "erroneous data" you are referring to, where as UNION ALL will just show everything.

In your example, try:

SELECT* FROM table1
UNION
SELECT* FROM table2
  • Yes I tried using union instead of union all. The same error pops up in value 2 in table three (it goes from being null to having some numerical value) – Peter Gibbs Jul 21 '17 at 20:05
  • Could it be that the actual value in table2.value2 is NULL while table3.value2 has a value of 594792? Try verifying this without the UNION. For example: SELECT * FROM table2 –  Jul 21 '17 at 20:15
  • I believe OP is thinking of a JOIN where val<> NULL, rather than a UNION but doesn't know it yet. – Isaiah3015 Jul 21 '17 at 20:20
  • @PhilK goot idea. I perfomred the following 3 queries: select * from table1 where column3 = 594792; select * from table2 where column3 = 594792; select * from join_global where column3 = 594792; The first two returned empty... the last query returned 1 row. I'm so confused :D – Peter Gibbs Jul 21 '17 at 20:27
  • I am ok retaining null values after the join. – Peter Gibbs Jul 21 '17 at 20:34
0

Problem solved. The columns were in a different order and therefore did not match up properly. I didn't realize the columns were in a different order. @Isaiah3015 I bet a sample of the database would have made this problem obvious. Thanks everyone for the help!

Peter Gibbs
  • 25
  • 9