CODEWITHSUNDEEP
mysqlsqljoin
akronymn
akronymn

Reputation: 2446

How to select possibly non-existant row in a mysql join

I have two tables that I need to join in a query that needs to return a single row. From one table I will be selecting a row that will always exist. From the second I need to select two rows, one of which will always exist and one which will exist only sometimes.

I can make this work in cases where both rows from the second table exist but not in cases where the second row does not exist.

Here's a simplified example of my schema

Table 1: COMPANIES

COMPANY_ID int

------------
|COMPANY_ID|
------------
|         1|
|         2|
------------

Table 2: PREFERENCES

COMPANY_ID int,
PREFERENCE_ID int,
PREFERENCE_VALUE varchar

-------------------------------------------
|COMPANY_ID|PREFERENCE_ID|PREFERENCE_VALUE|
-------------------------------------------
|         1|            1|             foo|
|         1|            2|             bar|
|         2|            1|             baz|
-------------------------------------------

What I am looking for is a query that will return a row with both PREFERENCE_IDs when COMPANY_ID = 1 and a row with one PREFERENCE_ID and on empty cell (or zero or any default value) when COMPANY_ID = 2.

I am trying to use a query like

select c.*, p1.PREFERENCE_VALUE as VALUE1, p2.PREFERENCE_VALUE as VALUE2 
  from 
    COMPANIES 
    join PREFERENCES p1 on c.ID = p1.COMPANY_ID 
    join PREFERENCES p2 on c.ID = p2.COMPANY_ID 
   where 
     p1.PREFERENCE_ID = 1 and 
     p2.PREFERENCE_ID = 2 and 
     COMPANIES.ID = 1;

When I run this query with COMPANY_ID = 1 (where there are PREFERENCE_IDs 1 and 2 it works fine. When I run it with COMPANY_ID = 2 it returns an empty set. I've tried using a left join for the second join but that doesn't change anything.

What I want it to return:

COMPANY_ID = 1 in query:

------------------
|ID|VALUE1|VALUE2|
------------------
| 1|   foo|   bar|
------------------

and for COMPANY_ID=2 it could return anything for VALUE2. null, blank, 0, I don't care:

------------------
|ID|VALUE1|VALUE2|
------------------
| 2|   baz|      |
------------------

TIA for any advice.

Upvotes: 0

Views: 1217

Answers (1)

Jeff Rosenberg
Jeff Rosenberg

Reputation: 3572

The problem is the WHERE clause. LEFT JOIN will return nulls and produce exactly what you're looking for, but the WHERE in your query negates that. 

select c.*, p1.PREFERENCE_VALUE as VALUE1, p2.PREFERENCE_VALUE as VALUE2 
  from 
    COMPANIES 
    inner join PREFERENCES p1 
      on c.ID = p1.COMPANY_ID and p1.PREFERENCE_VALUE = 1
    left outer join PREFERENCES p2 
      on c.ID = p2.COMPANY_ID and p2.PREFERENCE_VALUE = 2
   where 
     COMPANIES.ID = 1;

Upvotes: 2

Related Questions