CODEWITHSUNDEEP
phpmysqldatabase
user13580285
user13580285

Reputation: 13

"NOT NULL", "IS NOT NULL" does not display in the table

PROBLEM
Null column and Not null column does not display at the table

Explainaton
*I want to display all result at rank column from different database table which is Table 1 until table 4 below, but it only display for those who already have 4 result data. How can i achive this? Sorry im new in database field. I hope you guys can give me some reference or related *

My query

<?php
                   $sql = "SELECT virtualexam.rank.id, virtualexam.rank.username,

                   virtualexam.rank.score AS score, virtualexam1.rank.score AS 'score1', virtualexam2.rank.score AS 'score2', virtualexam3.rank.score AS 'score3',

                   SUM(virtualexam.rank.score + virtualexam1.rank.score + virtualexam2.rank.score + virtualexam3.rank.score ) AS 'total' 
                   
                   FROM virtualexam.rank
               
                   JOIN virtualexam1.rank ON (virtualexam.rank.username =  virtualexam1.rank.username) 
               
                   JOIN virtualexam2.rank ON (virtualexam2.rank.username =  virtualexam1.rank.username)
               
                  JOIN virtualexam3.rank ON (virtualexam3.rank.username =  virtualexam2.rank.username) 

                  WHERE virtualexam.rank.score IS NULL OR virtualexam.rank.score = ''
                 
                  GROUP BY virtualexam.rank.username ; ";


                  $result = $conn->query($sql);

?>

my html table

db.Table 1

db1.Table 2

db2.Table 3

db3.Table 4

Upvotes: 1

Views: 39

Answers (1)

ysth
ysth

Reputation: 98398

When you say just JOIN, it requires a record to exist in the joined table. If you want it to be optional, use LEFT JOIN instead. Then if the joined table doesn't exist, references to columns in it will give NULL. To keep your virtualexam.rank.score + virtualexam1.rank.score + virtualexam2.rank.score + virtualexam3.rank.score from returning NULL when any of the things being added are NULL, you need to use coalesce. So:

SELECT
    virtualexam.rank.id,
    virtualexam.rank.username,
    virtualexam.rank.score AS score,
    virtualexam1.rank.score AS 'score1',
    virtualexam2.rank.score AS 'score2',
    virtualexam3.rank.score AS 'score3',
    SUM(COALESCE(virtualexam.rank.score,0) + COALESCE(virtualexam1.rank.score,0) + COALESCE(virtualexam2.rank.score,0) + COALESCE(virtualexam3.rank.score) ) AS 'total' 
FROM virtualexam.rank
LEFT JOIN virtualexam1.rank ON (virtualexam.rank.username = virtualexam1.rank.username) 
LEFT JOIN virtualexam2.rank ON (virtualexam2.rank.username = virtualexam1.rank.username)
LEFT JOIN virtualexam3.rank ON (virtualexam3.rank.username = virtualexam2.rank.username) 
WHERE virtualexam.rank.score IS NULL OR virtualexam.rank.score = ''                 
GROUP BY virtualexam.rank.username

This still only reports users who have a virtualexam.rank entry. If you want to include those who don't but do have an entry in any of the other databases, you have to do:

FROM (
    SELECT DISTINCT username FROM virtualexam.rank
    UNION SELECT DISTINCT username FROM virtualexam1.rank
    UNION SELECT DISTINCT username FROM virtualexam2.rank
    UNION SELECT DISTINCT username FROM virtualexam3.rank
) AS users
LEFT JOIN virtualexam.rank ON virtualexam.rank.username=users.username
LEFT JOIN virtualexam1.rank ON virtualexam1.rank.username=users.username
LEFT JOIN virtualexam2.rank ON virtualexam2.rank.username=users.username
LEFT JOIN virtualexam3.rank ON virtualexam3.rank.username=users.username

But this is a very poor database design. Put all your scores in a single table, with other fields to identify what the score is for.

Upvotes: 1

Related Questions