CODEWITHSUNDEEP
sql
jantox
jantox

Reputation: 2185

How to do this using JOIN

I have 4 tables.

tbl_person:
  - person_id
  - unique_id_number

tbl_plan:
  - plan_id
  - plan_name

tbl_person_plan:
  - id
  - person_id
  - plan_id
  - is_done

tbl_monitor_level:
  - id
  - unique_id_number
  - sugar_level
  - date_submitted

Users will input data everyday in tbl_monitor_level. I just want to retrieve all unique_id_number where they haven't inputted any data for today.

I use this

select distinct tml.unique_id_number, count(tml.sugar_level) from 
tbl_person tp
right join
tbl_monitor_level tml on 
tml.unique_id_number = tp.unique_id_number
inner join tbl_person_plan tpp on 
tpp.person_id = tp.person_id
where tml.date_submitted = '2013-01-08'
group by tml.unique_id_number

but it will only get all the unique_id_number with a date_submitted = 2003-01-08 I want to get this instead

unique_id_number | count
-------------------------
G12345A          |   1
G23456B          |   0

But as it is it will not get the other unique_id_number (G23456B). All these unique_id_numbers exist on other tables.

Sample data

 tbl_person
    - person_id - 1
    - unique_id_number - G12345A

    - person_id - 2
    - unique_id_number - G23456B

 tbl_plan
    - plan_id - 1
    - plan_name - Plan A

 tbl_person_plan
    - id - 1
    - person_id - 1
    - plan_id - 1
    - is_done - 0 

    - id - 2
    - person_id - 2
    - plan_id - 1
    - is_done - 0

 tbl_monitor_level
    - id - 1
    - unique_id_number - G12345A
    - sugar_level - 100
    - date_submitted - 2013-01-08

    - id - 2
    - unique_id_number - G12345B
    - sugar_level - 200
    - date_submitted - 2013-01-07

The above data should be the result. But I am getting only 

unique_id_number | count
-------------------------
G12345A          |   1

Upvotes: 0

Views: 72

Answers (1)

PinnyM
PinnyM

Reputation: 35531

A LEFT JOIN should get this for you as follows:

SELECT tp.unique_id_number, COUNT(tml.id) AS count
FROM tbl_person tp
LEFT JOIN tbl_monitor_level tml
  ON tp.unique_id_number = tml.unique_id_number
  AND tml.date_submitted = '2013-01-08'
GROUP BY tp.unique_id_number

The primary difference is the LEFT vs RIGHT JOIN that you did, and that you need to group on the table that always has an entry - tbl_person in this case. Also, the WHERE clause needs to be merged into the LEFT JOIN itself to avoid excluding entries without matching monitor rows.

Here is a sqlfiddle of it working.

UNIQUE_ID_NUMBER | COUNT
G12345A          | 1      
G23456B          | 0

Just FYI, RIGHT JOINs are scarcely used as they usually serve to make the SQL logic more confusing than it already is ;)

Upvotes: 1

Related Questions