469,275 Members | 1,471 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,275 developers. It's quick & easy.

concat in select statement

Claus Mygind
571 512MB
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?

Expand|Select|Wrap|Line Numbers
  1. select  t1.*, concat(   ) as aFoundValue
  2. from table1 t1
  3. left join table2 t2 on t1.key=t2.key
  4. left join table3 t3 on t1.key=t3.key
  5.  
Mar 27 '09 #1
2 2285
Atli
5,058 Expert 4TB
Hi.

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

For example, if you have this data:
Expand|Select|Wrap|Line Numbers
  1. CREATE TABLE t1(id Serial Primary Key, txtValue varchar(10));
  2. CREATE TABLE t2(id Serial Primary Key, txtValue varchar(10));
  3.  
  4. INSERT INTO t1(txtValue) VALUES('First'), (Null), ('First'), (NULL), (NULL), ('First');
  5. INSERT INTO t2(txtValue) VALUES(NULL), ('Second'), (Null), ('Second'), ('Second'), (Null);
Then you could do this:
Expand|Select|Wrap|Line Numbers
  1. SELECT 
  2.   IF(IFNULL(t1.txtValue, true), 
  3.      t2.txtValue, 
  4.      t1.txtValue
  5.   ) AS 'Value'
  6. FROM t1 
  7. LEFT JOIN t2 
  8.   ON t1.id = t2.id;
Which would fill in the NULL fields from the first table with data from the second:
Expand|Select|Wrap|Line Numbers
  1. +--------+
  2. | Value  |
  3. +--------+
  4. | First  |
  5. | Second |
  6. | First  |
  7. | Second |
  8. | Second |
  9. | First  |
  10. +--------+
  11. 6 rows in set (0.00 sec)
Apr 8 '09 #2
Claus Mygind
571 512MB
Thank you very much for that very useful tip.
Apr 8 '09 #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

4 posts views Thread by Gerald Aichholzer | last post: by
3 posts views Thread by nickdevx | last post: by
4 posts views Thread by Martin Evans | last post: by
6 posts views Thread by GarryJones | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.