mysql join column if value is not null

Question

i try to join 3 column from my table into one, if value is not null. this is my table:

CREATE TABLE IF NOT EXISTS `roles_map` (
  `rm_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `username_a` varchar(45),
  `username_l` varchar(45),
  `username_u` varchar(45),
  `password` varchar(45) NOT NULL,
  `role_id` int(1) NOT NULL,
   PRIMARY KEY (`rm_id`),
   FOREIGN KEY (username_u) REFERENCES users(index_num),
   FOREIGN KEY (username_a) REFERENCES admins(login),
   FOREIGN KEY (username_l) REFERENCES lecturers(id_number),
   FOREIGN KEY (role_id) REFERENCES roles_name(role_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

i try to join username_l, username_u, username_a, because only one have value != null.

can somebody help me?

Have you tried anything? – Drixson Oseña Nov 09 '13 at 22:46 — Drixson Oseña, Nov 09 '13 at 22:46

Uours · Answer 1 · 2013-11-10T00:08:59.230

If you just want to retrieve the CONCATenated value , you could do like this :

SELECT
    CONCAT(
         IFNULL( `username_a` , '' )
        ,IFNULL( `username_l` , '' )
        ,IFNULL( `username_u` , '' )
    )
FROM `roles_map`

But if you want to store it in a new column say username , first add the column :

ALTER TABLE `roles_map` ADD `username` VARCHAR( 45 ) NOT NULL DEFAULT '';

Then use an UPDATE statement something like this :

UPDATE `roles_map`
    SET `username` =
        CONCAT(
             IFNULL( `username_a` , '' )
            ,IFNULL( `username_l` , '' )
            ,IFNULL( `username_u` , '' )
        )

To JOIN you could try something like this :

SELECT
    `roles_map`.*
    ,CONCAT(
         IFNULL( `username_a` , '' )
        ,IFNULL( `username_l` , '' )
        ,IFNULL( `username_u` , '' )
    ) AS username
FROM
    `roles_map`
    LEFT OUTER JOIN
        `admins` ON
            `admins`.`login` = 
            CONCAT(
                 IFNULL( `roles_map`.`username_a` , '' )
                ,IFNULL( `roles_map`.`username_l` , '' )
                ,IFNULL( `roles_map`.`username_u` , '' )
            )

Links : CONCAT , IFNULL

both option are good, but next i need to use this like this: SELECT CONCAT( IFNULL( `username_a` , '' ) ,IFNULL( `username_l` , '' ) ,IFNULL( `username_u` , '' ) ) as concat FROM `roles_map` where concat = 'admin' how can i do this? i cant use new column ;/ — tommy, Nov 09 '13 at 23:34

elixenide · Accepted Answer · 2013-11-10T00:24:57.683

0

JOIN is a technical term, meaning a cross-reference between two tables. It sounds like you are just trying to select the one non-null value, like this:

SELECT IFNULL(`username_l`, IFNULL(`username_u`, `username_a`)) AS `username`
FROM `roles_map`
WHERE IFNULL(`username_l`, IFNULL(`username_u`, `username_a`)) = 'admin'

edited Nov 10 '13 at 00:24

answered Nov 09 '13 at 22:49

elixenide

44,308
16
74
100

both option are good, but next i need to use this like this: SELECT CONCAT( IFNULL( `username_a` , '' ) ,IFNULL( `username_l` , '' ) ,IFNULL( `username_u` , '' ) ) as concat FROM `roles_map` where concat = 'admin' how can i do this? i cant use new column ;/ – tommy Nov 09 '13 at 23:35
See my edit. Note that this will not be a great way to do this, because it won't make good use of indexes. It would be better to reorganize the data if that's possible. – elixenide Nov 10 '13 at 00:28

mysql join column if value is not null

2 Answers2