CODEWITHSUNDEEP
mysqljoinsubquerydml
stackoverflow
stackoverflow

Reputation: 19474

MySQL How do I make this subquery run correctly?

I have the following database tables:

People
+--------+------------+------------+-----------+
|  f_ID  |  F_NAME    |    STUFF   | F_VALUE   | 
+--------+------------+------------+-----------+
|    1   |   Zoe      |    blah    |     101   |
|    2   |   Jimy     |    derka   |     202   |
|    3   |   Kate     |    shazam  |     20    |
|    4   |   Sue      |    pow     |      2    |
+--------+------------+------------+-----------+

RESULTS
+------- ---+------------+
| F_ID      |   RESULTS  | 
+-----------+------------+
| 1         |     Good   |
| 4         |      OK    |
+-----------+------------+

How do I create a query to display all entries in people table like this:

+------------+------------+
|   NAME     |    GRADE   | 
+------------+------------+
|   Zoe      |     Good   |
|   Jimy     |     N/A    |
|   Kate     |     N/A    |
|   Sue      |     OK     |
+------------+------------+

I tried this query but it doesn't work correctly:

SELECT 
NAME
GRADE
(
SELECT 
p.F_NAME AS NAME,
r.RESULTS

FROM People p
JOIN RESULTS r ON r.F_ID=p.F_ID

UNION

SELECT 
p.F_NAME AS NAME,
"N/A"

FROM People p
JOIN RESULTS r ON r.F_ID=p.F_ID
)a

Upvotes: 0

Views: 81

Answers (3)

John Woo
John Woo

Reputation: 263843

without using UNION, you can achieve this using LEFT JOIN

SELECT  a.F_Name, 
        COALESCE(b.Results, 'N/A') Grade
FROM    People a LEFT JOIN Results b ON
            a.f_IF = b.F_ID

Upvotes: 1

Aleksandar Vucetic
Aleksandar Vucetic

Reputation: 14953

You should use left join. You will get NULL for GRADE where records don't exist. When presenting the data, just put N/A instead of null.

select People.F_NAME as NAME, RESULTS.RESULTS AS GRADE
from People left join RESULTS ON People.f_ID = RESULTS.f_ID

or, if you want N/A instead of null, use IF

select People.F_NAME as NAME, IF(RESULTS.RESULTS is NULL, 'N/A', RESULTS.RESULTS) AS GRADE
from People left join RESULTS ON People.f_ID = RESULTS.f_ID

or using COALESCE (thanks @bfavaretto)

select People.F_NAME as NAME, COALESCE(RESULTS.RESULTS, 'N/A') AS GRADE
from People left join RESULTS ON People.f_ID = RESULTS.f_ID

Upvotes: 2

ncremins
ncremins

Reputation: 9200

Can be achieved with a join

SELECT people.f_name, IF(results.grade <> 0, results.grade, 'N/A') from people
JOIN results USING(f_id)

Upvotes: 1

Related Questions