Tan Tan - 1 year ago 65
SQL Question

SQL : Replace integer with string when there is empty value

I am doing something very similar to this SO thread (link). The difference is that my table sometimes has empty value. Here is the illustration below following the link that I provide.

1) Table 'people' that contain list of people with their favorite sport

ID | Name | FavSport | DislikeSport
------------------------------------
1 | Bob | 1 | 5
2 | Roy | 3 | 4
3 | Sarah | |
4 | Kevin | 2 |
5 | Mary | | 3


2) Table 'sports' that show the list of the sport

ID | Sport
---------------
1 | Football
2 | Basketball
3 | Volleyball
4 | Soccer
5 | Badminton


The query I get from the link I post in this thread

select people.Name, sports.Sport
from people, sports
where people.FavSport = sports.ID


3) The Result if the query similar like above

Name | Sport | Dislike
----------------------------
Bob | Football | Badminton
Roy | Volleyball | Soccer
Sarah | Football | Football
Kevin | Basketball | Football
Mary | Football | Volleyball


4) What I want is empty like below (or any default value like : nothing)

Name | Sport | Dislike
----------------------------
Bob | Football | Badminton
Roy | Volleyball | Soccer
Sarah | |
Kevin | Basketball |
Mary | | Volleyball


Anyone how to deal with this problem? Thanks in advance.

Answer Source

You need an outer join

select people.Name,
       likes.Sport as FavSport ,
       dislikes.Sport as DislikeSport
from people
left join sports likes
on people.FavSport = likes.ID
left join sports dislikes
on people.DislikeSport = dislikes.ID

Standard Docs for JOIN

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download