CODEWITHSUNDEEP
mysqlsql
Maxcot
Maxcot

Reputation: 1617

Are MySQL CASE statements prioritized?

I'm struggling with a query where there are some null values, and in my CASE statement, the test for the NULL values is not being processed. I've tried ordering the tests differently, but it doesn't seem to make a difference. 

SELECT Title, library.LibraryID, Registered
FROM libraries
LEFT JOIN 
(SELECT LibraryID,UserID,
        (CASE  
>>>                WHEN UserID IS NULL THEN "False"     <<<<<<<< PROBLEM
               WHEN UserID=1 THEN "YES! Registered" 
                   ELSE "Not Registered" 
     END) AS Registered
FROM librariesandmembers WHERE UserID=1) AS myAlias 
ON libraries.LibraryID = myAlias.LibraryID');

I've then tried a 

WHEN USerID<>1 THEN "False"

But that doesn't change anything.

This is a PHP/MySQL app. The results in phpmyAdmin shows that the results of my query are producing NULLS where I want "False" to appear.

What am I missing? Must be something obvious but I just cannot see it.

[EDIT] Let me try explain the objective this way: I want to have a list of all (e.g. 5) libraries with the result next to it that the user is "Yes! Registered" or "Not Registered". There may or may not be a corresponding entry in librariesandmembers table. hence the NULL problem.

Upvotes: 0

Views: 93

Answers (4)

Mario
Mario

Reputation: 196

How about

SELECT a.Title, a.LibraryID, IFNULL(myAlias.Registered,'Not Registered') Registered
FROM libraries a
     LEFT JOIN 
        (SELECT b.LibraryID, ('YES! Registered') Registered
         FROM librariesandmembers b
         WHERE b.UserID=1) AS myAlias  ON libraries.LibraryID = myAlias.LibraryID

Or this one

SELECT a.Title, 
       a.LibraryID, 
       IF((SELECT COUNT(*) 
           FROM librariesandmembers b
           WHERE b.LibraryID=a.LibraryID AND b.UserID=1)=0,'Not Registered','Yes! Registered') Registered
FROM libraries a

In your subquery there is only two posible values returned. The UserID or NULL when that user doesn't exists in members. That is why the CASE statement isn't working.

Upvotes: 0

Zec
Zec

Reputation: 843

I'm almost certain this is to do with your join rather than your case statement. That is, you build your subquery result set with the switch to handle NULLs, then LOJ that to libraries, effectively creating wholly NULL records where no match exists. Thus your efforts with the Registered column are for naught. Try re-structuring your query like so:

SELECT 
  Title, 
  libraries.LibraryID, 
  (
    CASE
      WHEN librariesandmembers.UserID IS NOT NULL THEN "YES! Registered"
      ELSE "Not Registered"
    END
  ) as Registered
FROM 
  libraries LEFT JOIN librariesandmembers
    ON libraries.LibraryID = librariesandmembers.LibraryID
    AND librariesandmembers.UserID = 1

Also, you are handling the UserID=1 criterion twice. Your subquery effectively removes all other UserID's and therefore you have no need of the ELSE clause (which handles when the UserID is neither 1 or NULL). I think you could move my suggested WHERE clause to be a second JOIN condition, but I think this would get you the same results. Perhaps someone can argue for/against my approach?

Upvotes: 1

Vignesh Kumar A
Vignesh Kumar A

Reputation: 28413

Try this

SELECT 
  Title, 
  libraries.LibraryID, 
  (
    CASE
      WHEN librariesandmembers.UserID IS Null OR librariesandmembers.UserID ='' THEN 'False'
      When librariesandmembers.UserID = 1 Then 'YES! Registered'
      ELSE "Not Registered"
    END
  ) as Registered
FROM 
  libraries LEFT JOIN librariesandmembers
    ON libraries.LibraryID = librariesandmembers.LibraryID
WHERE 
  librariesandmembers.UserID = 1

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270463

This is your query:

SELECT Title, l.LibraryID, Registered
FROM libraries lLEFT JOIN 
      (SELECT LibraryID, UserID,
              (CASE WHEN UserID IS NULL THEN "False"     <<<<<<<< PROBLEM
                    WHEN UserID=1 THEN "YES! Registered" 
                   ELSE "Not Registered" 
               END) AS Registered
       FROM librariesandmembers
       WHERE UserID = 1
      ) AS myAlias 
      ON l.LibraryID = myAlias.LibraryID;

Within the subquery, userId always has the value of 1, so the case statement only says 'YES! Registered'. You want to move that to the outer level. To get the same effect, the condition UserId=1 has to move to the on clause:

SELECT Title, l.LibraryID, Registered,
       (CASE WHEN myalias.UserID IS NULL THEN 'False'
             WHEN myalias.UserID = 1 THEN 'YES! Registered'
             ELSE 'Not Registered'
        END) AS Registered
FROM libraries l LEFT JOIN 
     librariesandmembers myalias
      ON l.LibraryID = myAlias.LibraryID and
         myalias.UserID = 1;

Upvotes: 1

Related Questions