Question 240443 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Receiving NULL output for some fields

I’m learning SQL from a website. The question they asked was

Show the provinces that has more patients identified as ‘M’ than ‘F’.
Must only show full province_name

Table

Table provided

My attempted answer using subqueries, rather than join (I am trying to learn subqueries rather than the right way which is to use "joins")

SELECT 
  (select pn.province_name where pn.province_id=p.province_id) AS Province
FROM 
  patients p,
  province_names pn
group by
  p.province_id
having 
  COUNT(case when p.gender = 'M' then 1 end) > count(case when p.gender = 'F' then 1 end)

But the output I get is

enter image description here

When I should actually get

enter image description here

Why are NULLS appearing?

Tags:

2

Answers


  1. 0

    perhaps province_names.province.name (TEXT) are NULL, so when you select them using

    select pn.province_name where pn.province_id=p.province_id

    You could be returning NULL values from the start.

    Login or Signup to reply.
  2. 0

    Consider using a coorlated subquery instead?

    SELECT 
  (select pn.province_name FROM province_Names pn where pn.province_id=p.province_id) AS Province
FROM 
  patients p
group by
  p.province_id
having 
  COUNT(case when p.gender = 'M' then 1 end) > count(case when p.gender = 'F' then 1 end)

    Though I’d prefer

    SELECT pn.province_name ) AS Province
FROM 
  patients p
LEFT JOIN province_Names pn on pn.province_id=p.province_id
group by
  p.province_id
having 
  COUNT(case when p.gender = 'M' then 1 end) > count(case when p.gender = 'F' then 1 end)
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search