Selecting data from two mysql tables where there may be nonexistent data in second table

2
votes

I have the following SQL statement

SELECT invoices.id, invoices.companyid IF( comp_companies.id = invoices.companyid, comp_companies.name, 'Deleted company' ) AS companyname
FROM invoices, comp_companies
WHERE invoice_name IS NULL
LIMIT 0 , 30

and I have 17 elements in my invoices table, 16 where invoice_name is null.

What I want to achieve is to select everything from invoices where invoice_name is NULL, and get the name of the company which is attached by a company id, directly from the query, or if there is no such company id in comp_companies table, get the text "Deleted Company" at the companyname.

This query returns 32 results, double the amount that are in the invoices table, two for each distinct invoice id, one with companyname as Deleted Company and one with companyname as the actual company name.

I already tried grouping by invoices.id, or selecting distinct invoices.id only, but nothing worked.

Can someone tell me what is the "problem" with my query, and how could I achieve the desired result?

2
mysqlsqlif-statement

2 Answers

1
votes

You need to select from invoices and join to comp_companies. Normally this is an inner join, but because the comp_companies row may not exist you need a left join:

SELECT
  invoices.id,
  invoices.companyid,
  COALESCE(comp_companies.name, 'Deleted company') as companyname
FROM invoices
LEFT JOIN comp_companies ON invoices.companyid = comp_companies.id
WHERE invoice_name IS NULL
LIMIT 0, 30

If there isn't a corresponding row in comp_companies, then comp_companies.name will be null. The COALESCE function returns the first non-null value passed to it, so when the company doesn't exist you'll get Deleted company.

There's a good visual description of inner and left joins here.

0
votes

Assuming there is no company name with null as its name, this should do the trick:

SELECT i.id, COALESCE(c.name, "Deleted Company") CompanyName FROM invoices i
LEFT JOIN comp_companies c ON i.companyid = c.id
WHERE i.invoice_name IS NULL
LIMIT 0, 30

If there is, then this will return "Deleted company" as the company name with that null value.