CODEWITHSUNDEEP
mysql
Brandon Macmillan
Brandon Macmillan

Reputation: 13

Including row in left join where data doesn't exist

I'm trying to create a query that shows me combined data between 2 tables

Currently the tables look like so:

person:ID,
Name,
Age


Form
ID,
Person_ID,
question,
answer


Example data would be
1 |Brandon| 55
2 |John |88
3 |Bob| 100


Form:
1 |1 |do you have diabetes |Y
2 |1 |do you take medications| Y
3 |1| how many times a week |7
4 |2|do you have diabetes |N

What I've tried is the following:

SELECT 
p.Name
er1.answer,
er2.answer,
er3.answer
from person p
LEFT JOIN form er1 ON p.ID = er1.Person_ID
LEFT JOIN form er2 ON p.ID = er2.Person_ID 
LEFT JOIN form er3 ON p.ID = er3.Person_ID
where er1.question='do you have diabetes'
and er2.question='do you take medications'
and er3.question='how many times a week'

The issue is that person 2 doesn't show up, because they answered No, the database didn't take in the other 2 questions. Is there a way to have the query search and pull the data and just have a null value there if it doesn't exist.

Upvotes: 0

Views: 467

Answers (1)

forpas
forpas

Reputation: 164089

Move the conditions in the ON clauses and remove the WHERE clause:

SELECT 
  p.Name,
  er1.answer,
  er2.answer,
  er3.answer
from person p
LEFT JOIN form er1 ON p.ID = er1.Person_ID AND er1.question='do you have diabetes'
LEFT JOIN form er2 ON p.ID = er2.Person_ID AND er2.question='do you take medications'
LEFT JOIN form er3 ON p.ID = er3.Person_ID AND er3.question='how many times a week'

It would be better if you gave proper aliases to the columns:

SELECT 
  p.Name,
  er1.answer as `do you have diabetes`,
  er2.answer as `do you take medications`,
  er3.answer as `how many times a week`
from person p
LEFT JOIN form er1 ON p.ID = er1.Person_ID AND er1.question='do you have diabetes'
LEFT JOIN form er2 ON p.ID = er2.Person_ID AND er2.question='do you take medications'
LEFT JOIN form er3 ON p.ID = er3.Person_ID AND er3.question='how many times a week'

See the demo.
Results:

| Name    | do you have diabetes | do you take medications | how many times a week |
| ------- | -------------------- | ----------------------- | --------------------- |
| Brandon | Y                    | Y                       | 7                     |
| John    | N                    |                         |                       |
| Bob     |                      |                         |                       |

Now consider another way of getting the results you want with conditional aggregation:

select 
  p.Name,
  max(case question when 'do you have diabetes' then answer end) as `do you have diabetes`,
  max(case question when 'do you take medications' then answer end) as `do you take medications`,
  max(case question when 'do you have diabetes' then answer end) as `how many times a week`
from person p LEFT JOIN form f ON p.ID = f.Person_ID 
group by p.ID, p.Name

See the demo.

Upvotes: 1

Related Questions