MYSQL: Display NULL value when foreing key is NULL

Question

I have the following database structure in MYSQL:

CREATE TABLE xs_bugtracking ( 
                    `id` INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, 
                    `product` VARCHAR(64) NOT NULL,
                    `title` VARCHAR(64) NOT NULL,
                    `status` VARCHAR(8) NOT NULL,
                    `descr` VARCHAR(1024) NOT NULL,
                    `assignee` INT(11),
                    `reported_by` INT(11) NOT NULL,
                    `importance` VARCHAR(8),
                    `create_date` INT(11) NOT NULL,
                    `modify_date` INT(11) NOT NULL
                    );

The fields assignee and reported_by are foreing key of the same table of wordpress db: wp_users

Now i want get all element with the following SELECT query:

SELECT xs_bugtracking.id, product, xs_bugtracking.title AS title, status, xs_bugtracking.descr AS descr,
            assignee_tbl.user_nicename AS assignee,reported_tbl.user_nicename AS reported_by,importance,
            FROM_UNIXTIME(create_date) AS 'create_date', FROM_UNIXTIME(create_date) AS 'modify_date' 
            FROM xs_bugtracking, xs_products, wp_users AS assignee_tbl,
            wp_users AS reported_tbl WHERE 
            xs_products.name=product AND xs_products.lang='en' AND
            (xs_bugtracking.assignee=assignee_tbl.ID OR ISNULL(xs_bugtracking.assignee)) AND
            xs_bugtracking.reported_by=reported_tbl.ID

Now, problems come when you have a row with assignee equals to NULL, infact the resulting table multiplies the rows when the value is NULL, because lost the JOIN from wp_users aka assignee_tbl.

There is a way to display assignee_tbl.user_nicename when assignee is not NULL and just NULL when assignee is NULL?

If you want check the full source code

Thanks in advance!

Answer 1

I suggest you use the ANSI join that was introduced in SQL92. If you use a LEFT JOIN I think you achieve what you want:

SELECT xs_bugtracking.id, product
     , xs_bugtracking.title AS title
     , status
     , xs_bugtracking.descr AS descr
     , assignee_tbl.user_nicename AS assignee
     , reported_tbl.user_nicename AS reported_by
     , importance
     , FROM_UNIXTIME(create_date) AS 'create_date'
     , FROM_UNIXTIME(create_date) AS 'modify_date' 
FROM xs_bugtracking
JOIN xs_products
    ON xs_bugtracking.product = xs_products.name 
LEFT JOIN wp_users AS assignee_tbl
    ON xs_bugtracking.assignee = assignee_tbl.ID
JOIN wp_users AS reported_tbl 
    ON xs_bugtracking.reported_by=reported_tbl.ID 
WHERE xs_products.lang='en' 

Answer 2

You have 3 joins using the old (implicit join) syntax that uses a comma separated list of tables in the FROM clause and the joining conditions in the WHERE clause:

FROM
    xs_bugtracking,
    xs_products,
    wp_users AS assignee_tbl,
    wp_users AS reported_tbl

WHERE 
      xs_products.name = product
  AND xs_products.lang = 'en'

  AND ( xs_bugtracking.assignee = assignee_tbl.ID
     OR ISNULL(xs_bugtracking.assignee)
      )

  AND xs_bugtracking.reported_by = reported_tbl.ID

While the syntax is valid, it allows only CROSS and INNER joins. In this case, it makes the (implicit) joins to be INNER joins. By the description of your issue, you need OUTER joins instead. So, the solution is to explixitly use LEFT (outer) joins:

FROM
    xs_bugtracking

  LEFT JOIN xs_products
  ON  xs_products.name = xs_bugtracking.product
  AND xs_products.lang = 'en'

  LEFT JOIN wp_users AS assignee_tbl
  ON xs_bugtracking.assignee = assignee_tbl.ID

  LEFT JOIN wp_users AS reported_tbl
  ON xs_bugtracking.reported_by = reported_tbl.ID

Answer 3

Currently your SQL Selected statement is an Inner Join. You need to refactor to use an outer join. I would suggest using alias where appropriate to help make the code mode readable.

SELECT 
    XSBug.id, 
    XSBug.product, 
    XSBug.title AS title, 
    status, 
    xs_bugtracking.descr AS descr,
    assignee_tbl.user_nicename AS assignee,
    reported_tbl.user_nicename AS reported_by,
    importance,
    FROM_UNIXTIME(create_date) AS 'create_date', 
    FROM_UNIXTIME(create_date) AS 'modify_date' 
FROM 
    xs_bugtracking XSBug
JOIN
    xs_products XSProd
ON
    XSPRod.Name = XSBug.Product AND xs_products.lang='en'
JOIN
    wp_users AS reported_tbl 
ON  
    XSBug.reported_by=reported_tbl.ID
LEFT OUTER JOIN
        wp_users AS assignee_tbl
ON
    XSBug = assignee_tbl.ID