0

I have two tables that I want to merge:

Table A

Site    Date      Time     Flow

xy-1    9/28/17   10:20    0.11

xy-1    8/13/17   9:59     

xy-1    7/30/17   9:38     0.15


Table B

Site    Date      Time     pH

xy-1    9/28/17   10:20    7.3

xy-1    8/27/17   10:30    7.8

xy-1    8/13/17   9:59     7.9

xy-1    7/30/17   9:38     7.2

I would like the output to look like this:

Site    Date      Time     pH   Flow

xy-1    9/28/17   10:20    7.3  0.11

xy-1    8/27/17   10:30    7.8

xy-1    8/13/17   9:59     7.9  

xy-1    7/30/17   9:38     7.2  0.15

I have tried various joins, but since the two tables have different numbers of records, rows are omitted during the join. Looking for SQL statements, Excel formulas, or Matlab code.

I would image it needs some sort of IF-statement that compares site ID and date. If site ID and date match in the two tables, both flow and ph are added. If site ID and date are different (or don't exist), a new row is created with records from only one table.

DanM
  • 7
  • 1
  • 3
  • 1
    Outer join will give you nulls when data is missing. It should achieve what you want – Sami Kuhmonen Oct 28 '17 at 18:00
  • re: '*I have tried various joins*' It seems like an outer or right join should suffice but you've decided that adding your own effort(s) (working or not) to the question was unimportant. –  Oct 28 '17 at 18:02
  • You can do this with `Get & Transform` or `Power Query` which are available in Excel 2010+. Excel formulas would be much trouble, but you could also use VBA – Ron Rosenfeld Oct 28 '17 at 19:57

1 Answers1

1

In Microsoft Access or MySQL, a UNION query can do the trick.

SELECT Site,    Date,      Time,    Null As pH,    Flow 
FROM TableA
UNION ALL
SELECT Site,    Date,      Time,    pH,    Null as Flow 
FROM TableB
Erik A
  • 31,639
  • 12
  • 42
  • 67
  • Thanks for the quick reply. This is definitely going towards the right direction, but it leaves the pH field blank and adds both flow and pH records to the flow column. Any suggestions are much appreciated. – DanM Oct 28 '17 at 18:48
  • That means you haven't added the `Null As pH` and the `Null as Flow ` columns as specified in the answer – Erik A Oct 28 '17 at 18:57
  • Order is important! Make sure the columns of both queries are in the right order, like in the answer. – Erik A Oct 28 '17 at 19:05
  • I had the order wrong and its working better, but it's still not combining records when necessary. For example, the record on 7/30/2017 should be one row – DanM Oct 28 '17 at 19:28
  • Is there any way you can change the code to achieve the desired output? Thanks for the help! – DanM Oct 28 '17 at 19:36
  • You can try https://stackoverflow.com/questions/34189693/how-to-merge-duplicate-rows on the result of this query – Erik A Oct 28 '17 at 19:51
  • Thank you. I can get the results I need with your code and EXCEL pivot tables. – DanM Oct 28 '17 at 19:59