concat in select statement

[Claus Mygind]

Is it possible to conduct a test in the select statement to see if a value is not found in one table (t2) then get the value from another table (t3).

In this case you would have 3 tables.
if there is no related record in table2 t2, then get the value from table3 t3

If so what would the code look like?

select  t1.*, concat(   ) as aFoundValue
from table1 t1
left join table2 t2 on t1.key=t2.key
left join table3 t3 on t1.key=t3.key
 

[Atli]

Hi.

You can use the IF and IFNULL functions to do that.

For example, if you have this data:

CREATE TABLE t1(id Serial Primary Key, txtValue varchar(10));
CREATE TABLE t2(id Serial Primary Key, txtValue varchar(10));
 
INSERT INTO t1(txtValue) VALUES('First'), (Null), ('First'), (NULL), (NULL), ('First');
INSERT INTO t2(txtValue) VALUES(NULL), ('Second'), (Null), ('Second'), ('Second'), (Null);

Then you could do this:

SELECT 
  IF(IFNULL(t1.txtValue, true), 
     t2.txtValue, 
     t1.txtValue
  ) AS 'Value'
FROM t1 
LEFT JOIN t2 
  ON t1.id = t2.id;

Which would fill in the NULL fields from the first table with data from the second:

+--------+
| Value  |
+--------+
| First  |
| Second |
| First  |
| Second |
| Second |
| First  |
+--------+
6 rows in set (0.00 sec)

[Claus Mygind]

Thank you very much for that very useful tip.