CODEWITHSUNDEEP
mysqlsql
user1515848
user1515848

Reputation: 43

Joining two tables when there is a not matching record exist

I am trying to use 1 sql statement to produce the result I want. I got 2 tables named , order and customers, and tried to use a query like this

select a.*, b.customers_name 
from order a, customers b 
where a.customers_id=b.customers_id   
order by b.customers_name;

My problem is there is a fake customers_id in order table, if customers_id=0 then customers_name='In House' which does not exist in cumstomers table. It's been used like this way before I joined this company so I can not modify the table at all.

Is there way to display the result? All order from order table with customers_name and if customers_id=0 (<= no match record in customers table) then customers_name='In House') and output should be ordered by customers_name.

Upvotes: 2

Views: 3179

Answers (5)

Ed Gibbs
Ed Gibbs

Reputation: 26363

This approach uses UNION to add the "customer 0" row during the query, then it does an INNER JOIN. Use it only if a LEFT JOIN doesn't work. Your order table obviously doesn't have a foreign-key relationship to customers (else you wouldn't be able to have a customer_id of 0), so a LEFT JOIN could pick up some garbage; you'll have to be the judge of that.

select a.*, b.customers_name
from order a
INNER JOIN (
  SELECT customers_id, customers_name
  FROM customers
  UNION SELECT 0, 'In House') b ON a.customers_id=b.customers_id
order by b.customers_name;

Upvotes: 0

Jay
Jay

Reputation: 27492

One option:

select a.*, case when a.customers_id=0 then 'In House' else b.customers_name end as customers_name
from order a
left join customers b on b.customers_id=a.customers_id;

BTW I suggest you use the "join" syntax. It's a lot easier to read then putting your join conditions in the "where" clause.

Upvotes: 0

beiller
beiller

Reputation: 3135

select a.*, IFNULL(b.customers_name, 'In House')
from order a
LEFT JOIN customers b ON a.customers_id=b.customers_id   
order by b.customers_name;

Heh we all got the same answers here...

Upvotes: 1

jesse_galley
jesse_galley

Reputation: 1676

You should be able to use a LEFT JOIN for this.

select a.*, b.customers_name
from order a
left join customers b
on a.customers_id = b.customers_id
order by b.customers_name;

Upvotes: 1

gbn
gbn

Reputation: 432657

select a.*,
        COALESCE(b.customers_name, 'In House') as customers_name
from
    order a LEFT JOIN customers b ON a.customers_id=b.customers_id
order by
    customers_name;

or

select a.*,
         CASE
           WHEN a.customers_id = 0 THEN 'In House'
           WHEN b.customers_name IS NULL THEN 'Unknown'
           ELSE b.customers_name
         END as customers_name
from
    order a LEFT JOIN customers b ON a.customers_id=b.customers_id
order by
    customers_name;

Either way, use an explicit JOIN for clarity.

The first one adds "in house" for any missing customers, the second one deals with missing customers by adding Unknown if customerid is not 0

Upvotes: 2

Related Questions