I know that I’m laying on the space analogies pretty thick, but it just seems to work so far… Anyway, I learned something interesting today and I thought that I would pass it on. I’d like to start off saying that I don’t know if this is the best way to go about trying to combine fairly unrelated records from fairly unrelated tables. If anyone has better ways to do this type of thing PLEASE let me know. I think given my constraints, this is the best way, but I would love to refactor if I could reduce the complexity.
Nevertheless, here goes. I had a situation where I had to pull records from two fairly unrelated tables that really were related based on certain conditions. One table (table B) was actually the child of a child of the other table, but there was not always a “grandparent” record in the other table (table A). Basically, I needed the record from the “grandchild” table (table B) unless it had a “grandparent” record (in table A), in which case I needed to see that record. That wouldn’t be a huge deal if the tables shared column names and primary keys, but they didn’t and we didn’t want to do a whole redesign at this point just to satisfy this “list” view of the combined records. What I needed was to be able to alias null values for the columns that the tables didn’t have in common so I could do two different selects with a SQL UNION. It took me a while to find some solutions, but this blog is where I found my answers. Here’s how I did it:
| Table A | |||
|---|---|---|---|
| primary key | col 1 | col 2 | col 3 |
| 1 | aaa | bbb | ccc |
| 2 | ddd | eee | fff |
| 3 | ggg | hhh | iii |
| 4 | jjj | kkk | lll |
| Table B | |||
|---|---|---|---|
| composite key 1 | composite key 1 | col 1 | col 2 |
| 1 | 1 | mmm | nnn |
| 1 | 2 | ooo | ppp |
| 2 | 1 | qqq | rrr |
| 2 | 2 | sss | ttt |
You can see that there a some common columns, but obviously the primary keys need to be separated and I need one column that doesn’t exist in the other table. When using the UNION operator, though, the number of columns and their names need to be the same. Plus, as I mentioned some of the records in table B might have a related record two generations away (really just linked through one other table) in table A, but in that case I only want to see the record from table A. The approach I wanted to use was to have the columns that did not exist in table A to be there in the results, but be null in value and vice-versa the other direction. I found out that you could call a function that cast “null” as the correct data type and then alias it to make this happen. Basically, to get null integers (for the primary keys) I used the CAST() function like this:
CAST(NULL AS INTEGER)
And for the etxtra column that is of type VARCHAR with a size of 25:
CAST(NULL AS VARCHAR(25))
To select the records from table A while creating the columns for table B’s composite key with null values (notice the aliasing):
SELECT Table_A.primary_key, CAST(NULL AS INTEGER) AS composite_key_1, CAST(NULL AS INTEGER) AS composite_key_2, Table_A.col_1, Table_A.col_2, Table_A.col_3 FROM Table_A
To select the records from Table_B while creating the columns for Table_A’s primary key and extra column with null values is a little trickier because I don’t want the records from Table_B that have a “grandparent” in Table_A. I found that if I joined to “up” to Table_A, Table_A’s primary key would be null (seems kind of obvious now) and I could specify that I only wanted the ones that were null (had no related record in Table_A):
SELECT CAST(NULL AS INTEGER) AS primary_key, Table_B.composite_key_1, Table_B.composite_key_2, Table_B.col_1, Table_B.col_2, CAST(NULL AS VARCHAR(25)) AS col_3 FROM Table_B LEFT INNER JOIN Intermediate_Table ON Table_B.foreign_key = Intermediate_Table.foreign_key LEFT INNER JOIN Table_A ON Intermediate_primary_key = Table_A.primary_key WHERE primary_key IS NULL
Then if the two selects are put together with a UNION, you get the entire list how it was required.
SELECT Table_A.primary_key, CAST(NULL AS INTEGER) AS composite_key_1, CAST(NULL AS INTEGER) AS composite_key_2, Table_A.col_1, Table_A.col_2, Table_A.col_3 FROM Table_A UNION SELECT CAST(NULL AS INTEGER) AS primary_key, Table_B.composite_key_1, Table_B.composite_key_2, Table_B.col_1, Table_B.col_2, CAST(NULL AS VARCHAR(25)) AS col_3 FROM Table_B LEFT INNER JOIN Intermediate_Table ON Table_B.foreign_key = Intermediate_Table.foreign_key LEFT INNER JOIN Table_A ON Intermediate_primary_key = Table_A.primary_key WHERE primary_key IS NULL
| Result set | |||||
|---|---|---|---|---|---|
| primary key | composite key 1 | composite key 2 | col 1 | col 2 | col 3 |
| 1 | null | null | aaa | bbb | ccc |
| 2 | null | null | ddd | eee | fff |
| 3 | null | null | ggg | hhh | iii |
| 4 | null | null | jjj | kkk | lll |
| null | 1 | 1 | mmm | nnn | null |
| null | 1 | 2 | ooo | ppp | null |
| null | 2 | 1 | qqq | rrr | null |
| null | 2 | 2 | sss | ttt | null |
You can see where the columns have been created where they didn’t exist before and are filled with null values. In fact you could fill the result set with any values, but for this purpose null had the most (or least) meaning.
