Joining two tables with sometimes missing join values

joins with null values examples
cross join
join with null values oracle
cross join vs inner join
when to use cross join
full outer join
cross join with where clause
left outer join with null values

I have two tables to join with two columns to join them on like person_id, component. For one person there can be multiple components, but the components can be the same for different person.

The problem is that one of the tables has all the correct data but the other has some missing values on the second column.

The two tables look something like this:

First table:

+----------+-----------+
| person_id|  component| 
+----------+-----------+
| sth      |   A       |  <-- 
| sth      |   B       |  <-- two components for sth
| sth1     |   A       |  <-- A component for sth1
| sth2     |   A       | 
| sth3     |   B       |  <-- B component for sth3 
+----------+-----------+

Notice that there is only one component for both sth1 and 3

Second table:

+----------+-----------+-------+
| person_id|  component| value |
+----------+-----------+-------+
| sth      |   A       | 1     |
| sth      |   B       | 21    |
| sth1     |   null    | 313   |<--should be matched with A component from sth1
| sth2     |   A       | 2     |
| sth3     |   null    | 12    |<--should be matched with B component from sth3
+----------+-----------+-------+

Notice that these null values can only be matched to a single row of table A.

So I need the values from the second table but i also need the first to match the null components to actual components. As you can see the missing component is not always the same.

If the second table has a missing component for person_id sth1 then the first table will only have ONE component for that id so it doesn't need to choose somehow just simply say if table B has a missing value at the component then it should be matched with table A's person id.

And of course I can't simply match person id-s because it won't know which value matches which component.

you can try below -

select a.person_id,a.component,value
from first_table  a
inner join second_Table b on a.person_id=b.person_id and a.component=b.component
union 
select a.person_id,a.component,value
from first_table  a
inner join second_Table b on a.person_id=b.person_id
where b.component is null

After joining two Tables, in the resulting Table the second join , We'll occasionally send you account related emails. After joining two Tables, in the resulting Table the second join column is missing. in join, I could not make operations with the second column as it was missing from the result. Shouldn't the second column and first column contain the same values? I have two tables to join with two columns to join them on like person_id, component. For one person there can be multiple components, but the components can be the same for different person.

First You can try joining person id and component then union the output of joining by person id when the component is Null

Select a.person_id,a.component,b.value 
FROM table1 a
INNER JOIN table2 b
on a.person_id=b.person_id and a.component=b.component

UNION ALL

Select a.person_id,a.component,b.value 
FROM table1 a
INNER JOIN table2 b
on a.person_id=b.person_id 
WHERE component is NULL

Learn How to Combine Data with a CROSS JOIN, Database Joins – Introduction to Join Syntax and Concepts. report this ad. A cross join is used What is a CROSS JOIN? Cross joins are used to return every combination of rows from two tables, this sometimes called a Cartesian product. But notice that there are some missing combinations. For instance, even though� Notice that the "CustomerID" column in the "Orders" table refers to the "CustomerID" in the "Customers" table. The relationship between the two tables above is the "CustomerID" column. Then, we can create the following SQL statement (that contains an INNER JOIN), that selects records that have matching values in both tables:

If I understand correctly, this is a problem where you are using null for a default. If so, I usually handle this with left joins:

select t1.person_id, t1.component,
       coalesce(t2.value, t2default.value) as value
from first_table t1 left join
     second_Table t2
     on t1.person_id = t2.person_id and t1.component = t2.component left join
     secondtable t2default
     on t1.person_id = t2.person_id and
        t2.component is null;

This has two advantages over union. First, it does not require removal of duplicate values. Second, this logic ensures that all rows in the first table are in the result set.

Join SQL Server tables where columns include NULL values, Dealing with NULL values especially when joining tables can become Example 2: When these two options are ON the default nullable value� The Relationship canvas is what you see when you first open or create a data source. For more information, see Relate Your Data. When joining tables, the fields that you join on must have the same data type. If you change the data type after you join the tables, the join will break. Published data sources cannot be used in joins.

Chapter 6 Joining tables, In many real life situations, data are spread across multiple tables or a set of join functions for combining two data frames based on matches within that fully joins two tables and keeps all observations, adding missing values if necessary. Sometimes, we want to be selective, and keep observations that are present in� Add matching values to the linking fields in both tables in the data source. Option 2 Rearrange tables in the join. The table that contains all possible values in the linking field should be the leftmost table in the join, when the join type is Left Outer Join. (If neither table contains all possible values, this option will not work.) Option 3

Solved: How do I join two data tables without losing data?, When I get the new output table, it is missing data. I have selected 'Merge same name columns' and 'Include non-matches' before creating the� You can also join tables that have missing values. For example, ID 3 is missing from Table B. A left join outputs all the names in Table A (the left table) and matches them to the available salary information in Table B. If a row in Table B is missing, missing values are supplied in the joined output.

SQLite Self-join - Joining a Table to Itself, The self-join is a special kind of joins that allow you to join a table to itself using either The self-join compares values of the same or different columns in the same table. You often use self-join to query parents/child relationship stored in a table or to obtain The employees table has two roles: employees and managers. Joins the rows of table1 with the rows of table2 based on the equality of the values of the key columns selected by key1 (for table1) and key2 (for table2). By default, an inner join is performed, however an optional joinKind may be included to specify the type of join. Options include: JoinKind.Inner