Mysql – right join leaving one row from right table

Perhaps I'm misunderstanding your request, but based on the text in your question that sounds simple enough:

SELECT
    U.first_name AS "User Name",
    U.email_id AS "User E-Mail Address",
    BossUser.first_name AS "Authorizing User Name"
    BossUser.email_id AS "Authorizing User E-Mail Address"
FROM
    user_login AS UL
    INNER JOIN users AS U ON UL.user_id = U.user_id
    LEFT JOIN  user_login AS BossLogin ON U.created_by = BossLogin.user_name
    LEFT JOIN  users AS BossUser ON BossLogin.user_id = BossUser.user_id

This allows for users without a created_by; if that field is NOT NULL, you can replace the LEFT JOINs with INNER JOIN.

First of all, this is my first post on dba.stackexchange.com, so I apologize in advance if there is any etiquette that I fail to follow, and am open to your input as to how I can improve future posts.

Best database design practices would call for consolidating the two very similar tables into just one table, like sa555 described. The issue you are currently facing is the direct consequence of having these two tables separate.

However, since you are working with a live system, I would recommend asking yourself and/or the rest of your development team (if applicable) the following question:

• Will the future benefits of consolidating these two tables outweigh the time and effort put into the process of consolidating the two tables? Keep in mind that any applications depending on these two tables being separate will need to be updated also.

If the benefits outweigh the costs, I would recommend consolidating the two tables, just as sa555 recommended. If not, I have included the MySQL code below to create a stored procedure that will return the most recent row from either table. Just remember that this is a mere surface-level fix, and that your database still contains a design flaw that could impede future development.

Here are some important notes on the procedure's edge-case behavior:

• If both tables are empty, the procedure will not return a result set at all.
• If only one of the tables contains records, the most recent record from the non-empty table will be returned.
• If the most recent records from both tables have the exact same date and time, the record from tblGAlert will be returned. You can return the tblEAlert record by changing the inequality from ">=" to ">". (comments will explain where to do this)
• Since both tables have different column names, you may want to canonicalize the column names in this procedure's result set using column name aliases (e.g. SELECT eDateTime AS AlertDateTime). The comments I have inserted into the code will direct you as to where to insert the aliases.

SQL Query (creates stored procedure):

DELIMITER $$
CREATE PROCEDURE GetLatestAlert()
BEGIN
-- Select latest gAlert record
SELECT gDateTime, gAlertID INTO @gAlertDateTime, @gID FROM tblGAlert ORDER BY gDateTime DESC LIMIT 1;

-- Select latest eAlert record
SELECT eDateTime, eAlertID INTO @eAlertDateTime, @eID FROM tblEAlert ORDER BY eDateTime DESC LIMIT 1;

-- We still want to be able to return a record in the event
-- that one of the tables does not contain a record, so we make
-- the default @gAlertDateTime and @eAlertDateTime values equal to the earliest
-- MySQL date possible, instead of the default value of null (if
-- no records were returned from that respective table). This is
-- because the NULL value cannot be compared using the ">" operator
-- that appears later on in this procedure.
IF @gAlertDateTime IS NULL THEN
    SET @gAlertDateTime = "1000-01-01 00:00:00"; -- MySQL earliest date value
END IF;

IF @eAlertDateTime IS NULL THEN
    SET @eAlertDateTime = "1000-01-01 00:00:00"; -- MySQL earliest date value
END IF;

-- Before comparing the dates, make sure one or both of the tables contain(s) a record.
IF @gID IS NOT NULL OR @eID IS NOT NULL THEN

  -- Compare the dates and return the latest row.
  -- Note: if one of the tables does not contain a record,
  -- the default values for @gAlertDateTime and @eAlertDateTime are the earliest
  -- possible date in MySQL, which will automatically make the
  -- logic choose the table that contains the record.
  IF @gAlertDateTime >= @eAlertDateTime THEN 
     -- Change the inequality from ">=" to ">" in order to return the tblEAlert record
     -- in the event that both records have the same date and time.

     -- NOTE: You may want to canonicalize the column names for the tblGAlert and 
     -- tblEAlert results by adding column aliases. This way the application using
     -- the data will get consistent column names.
     SELECT * FROM tblGAlert WHERE gAlertID = @gID;
  ELSE
     -- If you canonicalize the column names, insert the aliases here as well.
     SELECT * FROM tblEAlert WHERE eAlertID = @eID;
  END IF;

END IF;

-- If no records were returned from either table, this procedure
-- will not return a resultset at all.
END; $$

Now just execute CALL GetLatestAlert(); and the most recent row from either table will be returned.