CODEWITHSUNDEEP
sqlsql-server
Roberto Lopez
Roberto Lopez

Reputation: 55

SQL Query - Add column data from another table adding nulls

I have 2 tables, tableStock and tableParts:

tableStock
+----+----------+-------------+
| ID | Num_Part | Description |
+----+----------+-------------+
| 1  | sr37     | plate       |
+----+----------+-------------+
| 2  | sr56     | punch       |
+----+----------+-------------+
| 3  | sl30     | crimper     |
+----+----------+-------------+
| 4  | mp11     | holder      |
+----+----------+-------------+

tableParts
+----+----------+-------+
| ID | Location | Stock |
+----+----------+-------+
| 1  | A        | 2     |
+----+----------+-------+
| 3  | B        | 5     |
+----+----------+-------+
| 5  | C        | 2     |
+----+----------+-------+
| 7  | A        | 1     |
+----+----------+-------+

And I just want to do this:

+----+----------+-------------+----------+-------+
| ID | Num_Part | Description | Location | Stock |
+----+----------+-------------+----------+-------+
| 1  | sr37     | plate       | A        | 2     |
+----+----------+-------------+----------+-------+
| 2  | sr56     | punch       | NULL     | NULL  |
+----+----------+-------------+----------+-------+
| 3  | sl30     | crimper     | B        | 5     |
+----+----------+-------------+----------+-------+
| 4  | mp11     | holder      | NULL     | NULL  |
+----+----------+-------------+----------+-------+

List ALL the rows of the first table and if the second table has the info, in this case 'location' and 'stock', add to the column, if not, just null.

I have been using inner and left join but some rows of the first table disappear because the lack of data in the second one:

select tableStock.ID, tableStock.Num_Part, tableStock.Description, tableParts.Location, tableParts.Stock from tableStock inner join tableParts on tableStock.ID = tableParts.ID;

What can I do?

Upvotes: 0

Views: 30

Answers (1)

zealous
zealous

Reputation: 7503

You can use left join. Here is the demo.

select
    s.ID,
    Num_Part,
    Description,
    Location,
    Stock
from Stock s
left join Parts p
on s.ID = p.ID
order by
    s.ID

output:

| id  | num_part | description | location | stock |
| --- | -------- | ----------- | -------- | ----- |
| 1   | sr37     | plate       | A        | 2     |
| 2   | sr56     | punch       | NULL     | NULL  |
| 3   | sl30     | crimper     | B        | 5     |
| 4   | mp11     | holder      | NULL     | NULL  |

Upvotes: 1

Related Questions