How to insert NULL value in MySQL column with no match?

Question

I have the following two tables:

projects_expended:

+---------------+------------+----------+
| project_name  | project_id | Utilised |
+---------------+------------+----------+
| CStreamTax    |          1 |     3500 |
| CStreamTravel |          2 |      149 |
+---------------+------------+----------+

projects:

+------------+---------------------+
| project_id | project_budgetstart |
+------------+---------------------+
|          1 |               15000 |
|          2 |               15000 |
|          3 |                 500 |
+------------+---------------------+

I would like to make a joint table of the two, but with the Utilised field filled with NULL when a project_id form the projects table does not match one of the entries in the projects_expended table.

My basic attempt was as follows:

SELECT projects.*, projects_expended.utilised
  FROM projects_expended
  JOIN projects
    ON projects.project_id = projects_expended.project_id;

And the result:

+------------+---------------------+----------+
| project_id | project_budgetstart | utilised |
+------------+---------------------+----------+
|          1 |               15000 |     3500 |
|          2 |               15000 |      149 |
+------------+---------------------+----------+

I understand why I get the result I get. But I'm pretty stuck with this. I've made other attempts (too many to count).

Indeed. Will mark it as such. – PleaseWait Apr 30 '15 at 08:09 — PleaseWait, Apr 30 '15 at 08:09

Jens · Answer 1 · 2015-04-30T08:05:22.860

0

Use left outer join:

SELECT projects.*, projects_expended.utilised
  FROM projects
  LEFT OUTER JOIN projects_expended

    ON projects.project_id = projects_expended.project_id;

edited Apr 30 '15 at 08:05

answered Apr 30 '15 at 07:59

Jens

67,715
15
98
113

Thanks for responding. But that gave me the same results as my normal JOIN. – PleaseWait Apr 30 '15 at 08:04
@PleaseWait Sorry my fault. See my updated answer – Jens Apr 30 '15 at 08:05

score 0 · Answer 2 · edited May 23 '17 at 12:28

0

I found my answer from:

LEFT JOIN vs. LEFT OUTER JOIN in SQL Server

It was pretty helpful.

The solution:

SELECT projects.*, projects_expended.utilised
  FROM projects_expended
 RIGHT JOIN projects
    ON projects.project_id = projects_expended.project_id;

edited May 23 '17 at 12:28

Community

1
1

answered Apr 30 '15 at 08:04

PleaseWait

1

How to insert NULL value in MySQL column with no match?

2 Answers2