Mysql query : If value(s) exist(s) in column, select just the value(s), else select all

Question

Having a Mysql database, I have to select all ( TUser.Name, TAccount.Name ) pairs if TUser.name not exists in variable @UserNames.
If exists, I have to select only the ( TUser.Name, TAccount.Name ) pairs where TUser.name in @UserNames.

Something like the last line of the below query :

DECLARE @UserNames = "Alpha, Beta, Gama";

SELECT User.Name
     , Account.Name
  FROM TUser
     , TAccount
     , TUserAccount
 WHERE TAccount.ID = TUserAccount.AccountID
   AND TUserAccount.UserID = User.ID
-- please rewrite this line
   AND TUser.Name IN ( IFNULL ( ( SELECT ID FROM TUser WHERE Name IN @UserNames ) , ( SELECT ID FROM TUser ) ) )

Thank you in advance !

Answer 1

SELECT
    User.Name,
    Account.Name
    FROM TUser
        JOIN TAccount
            ON TAccount.Name = TUser.Name
        JOIN TUserAccount
            ON TUserAccount.AccountID = TAccount.ID
            AND TUserAccount.UserID = TUser.ID
        LEFT JOIN (
            SELECT ID FROM TUser WHERE Name IN @UserNames
        ) AS UsersFound
            ON TUser.ID = UsersFound.ID

Something like that? I haven't tested it though.

Answer 2

You can't return mutually exclusive result sets with your criteria, without using a IF statement:

SELECT @sum := SUM(FIND_IN_SET(u.name, @UserNames))
  FROM TUSER u

IF @sum > 0 THEN

    SELECT u.name,
           a.name
      FROM TUSER u
      JOIN TUSERACCOUNT ua ON ua.userid = u.id
      JOIN TACCOUNT a ON a.id = ua.accountid
     WHERE FIND_IN_SET(u.name, @UserNames) > 0

ELSE

    SELECT u.name,
           a.name
      FROM TUSER u
      JOIN TUSERACCOUNT ua ON ua.userid = u.id
      JOIN TACCOUNT a ON a.id = ua.accountid

END IF;

You could make that work as a PreparedStatement, MySQL's dynamic SQL, but you still need to run a query to know if you need to return all or a subset.

References:

• FIND_IN_SET
• IF statements

Answer 3

Ok, so this is what I would call 'hacks' rather than normall database querying, and I would disrecommend getting in the position you have to deal with in the first place. The right way to deal with a list of items like this is to have your application language parse it into a nice list you can use to build a normal, regular SQL IN list, like so:

TUser.Name IN ('Name1', 'Name2',...., 'NameX')

But anyway, if you want to stick to the original problem and your database is mysql, you can do it in a remotely sane way in a single query like this:

SET @UserNames := 'Alpha,Beta,Gama';

SELECT     TUser.Name
,          TAccount.Name
FROM       TUser
INNER JOIN TUserAccount
ON         TUser.ID               = TUserAccount.UserID
INNER JOIN TAccount
ON         TUserAccount.AccountID = TAccount.ID
WHERE      FIND_IN_SET(TUser.Name, @UserNames)
OR        (SELECT  COUNT(*) 
           FROM    TUser.Name
           WHERE   FIND_IN_SET(TUser.Name, @UserNames))  = 0

Note that I did change the input data somewhat - I don't have any spaces behind the commas. If that is unacceptable, simply change each occurrence of @UserNames in the query with REPLACE(@UserNames, ', ', ','). Also please note that it's performance down the drain as it is impossible to use any indexes on TUser.Name to filter for specific users.

I already mentioned that you really should make a proper IN list of your data. And you can do so directly in SQL too (dynamic SQL):

SET @UserNames := 'Alpha, Beta, Gama';

SET @stmt := CONCAT(   
 ' SELECT     TUser.Name' 
,' ,          TAccount.Name'
,' FROM       TUser'
,' INNER JOIN TUserAccount'
,' ON         TUser.ID               = TUserAccount.UserID'
,' INNER JOIN TAccount'
,' ON         TUserAccount.AccountID = TAccount.ID'
,' WHERE      TUser.Name IN (''', REPLACE(@UserNames, ', ', ''',''') , ''')'
,' OR        (SELECT  COUNT(*) '
,'            FROM    TUser.Name'
,'            WHERE   TUser.Name IN (''', REPLACE(@UserNames, ', ', ''',''') , '''))  = 0'
)

PREPARE stmt FROM @stmt;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

(Note that in this case, I could use the user name list with spaces unaltered)