mysql empty table,如何在MySQL中使用空表联接表

I have four table like this:

companies (title, description)

company_users (company_id, user_id)

users (id, group_id)

user_profiles (user_id, fullname)

I want to list campaigns.

My code is this:

SELECT companies.*, user_profiles.fullname as company_contact

FROM companies

INNER JOIN company_users ON companies.id = company_users.company_id

INNER JOIN users ON company_users.user_id = users.id AND users.group_id=500

INNER JOIN user_profiles ON users.id = user_profiles.user_id;

When i add a new campaign does not appear. Because company_id not in company_users.

How can i fix this?

Note: Eeach campaign must have only one user. This user's group_id is 500.

Thank You

Edit:

Tables:

companies

1 | Test Company | Test Desciption

2 | Test Company 2 | Test Description 2

company_users

1 | 18 | 1

2 | 19 | 1

users

18 | 500

19 | 900

user_profiles

18 | 18 | Test Contact

19 | 19 | Test User

LEFT JOIN Result:

1 Test Company Test Description Test Contact

1 Test Company Test Description NULL

2 Test Company 2 Test Description 2 NULL

INNER JOIN Result:

1 Test Company Test Description Test Contact

I need this:

1 Test Company Test Description Test Contact

1 Test Company 2 Test Description 2 NULL

解决方案

It sounds like we want outer join operations.

We can replace all occurrences of the keyword INNER with the keyword LEFT.

Then the query will return rows from companies even when no matching row is found in company_users.

EDIT

In response to question edit that provides additional information.

If we want to return every row in companies, along with the fullname of a single row from a related user (as shown in the desired resultset), we could use a correlate subquery in the SELECT list, rather than a join operation.

For example:

SELECT c.*

, ( SELECT p.fullname

FROM company_users cu

JOIN users ON u.id = cu.user_id AND u.group_id=500

JOIN user_profiles p ON p.user_id = u.id

WHERE cu.company_id = c.id

ORDER BY p.fullname ASC

LIMIT 1

) AS company_contact

FROM companies c

The subquery includes a reference to the id column in companies. For each row returned from the outer query (each row from companies), the subquery in the SELECT list is executed, using the id value from the companies row,

If the subquery doesn't find a row to return, a NULL value is returned. The LIMIT 1 clause ensures that we return at most one row (if the subquery returns more than one row, there will be an error returned.) The ORDER BY is included just to make the result more deterministic. That is, if the subquery happens to return more than one row, the ORDER BY will sort the rows, and the LIMIT 1 will get the first of the sorted rows.