MYSQL: Display NULL value when foreing key is NULL

foreign keyjoin;MySQLnullPHP

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!

Best Answer

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'