Select Fields From Table - MYSQL JOIN

Question

I am having table x.

x
............
id    email      first_name   last_name
'''''''''''''''''''''''''''''''''''''''
 1  [email protected]  Martin        Robert
 2  [email protected]     
 3  [email protected]  Sam           Anderson

I am using following query and the output is:

 select id, CONCAT(first_name, ' ', last_name) from a;

 The output is
 .......................................
 id    CONCAT(first_name, ' ', last_name)
 '''''''''''''''''''''''''''''''''''''''
  1    Martin Robert
  2
  3    Sam Anderson

I Want to ignore the row which has both first_name and last_name is empty in CONCAT().

My output will be as follows

  .......................................
 id    CONCAT(first_name, ' ', last_name)
 '''''''''''''''''''''''''''''''''''''''
  1    Martin Robert
  3    Sam Anderson

Thanks in advance.

Then you need a WHERE clause in your query

RiggsFolly
– RiggsFolly

2019-02-21 09:56:11 +00:00
Commented Feb 21, 2019 at 9:56 — RiggsFolly
– RiggsFolly, Commented Feb 21, 2019 at 9:56

Vijunav Vastivch · Accepted Answer · 2019-02-21 09:56:16Z

1

This way:

select id, CONCAT(first_name, ' ', last_name) from a where first_name is not null and last_name is not null;

answered Feb 21, 2019 at 9:56

Vijunav Vastivch

4,2111 gold badge18 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

TheMaker · Accepted Answer · 2019-02-21 09:59:27Z

1

SELECT ID, CONCAT(FIRST_NAME, ' ', LAST_NAME)
FROM TABLE_NAME_A
WHERE FIRST_NAME IS NOT NULL
AND LAST_NAME IS NOT

answered Feb 21, 2019 at 9:59

TheMaker

111 bronze badge

1 Comment

tbraun89 Over a year ago

While this code may solve the question, including an explanation of how and why this solves the problem would really help to improve the quality of your post, and probably result in more up-votes. Remember that you are answering the question for readers in the future, not just the person asking now. Please edit your answer to add explanation, and give an indication of what limitations and assumptions apply.

ScaisEdge · Accepted Answer · 2019-02-21 10:00:24Z

1

You need where clause

assuming the empty value mean null value

 select id, CONCAT(first_name, ' ', last_name) 
 from a 
 where first_name is NOT null 
 and last_name is NOT null;

or for test also empty string

 select id, CONCAT(first_name, ' ', last_name) 
 from a 
 where ifnull(first_name,'') = '' 
 and ifnull(last_name,'') =  '';

edited Feb 21, 2019 at 10:00

answered Feb 21, 2019 at 9:56

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

3 Comments

RiggsFolly Over a year ago

Might need to add a and first_name != '' as that may not actually be null :)

Vijunav Vastivch Over a year ago

What is the difference this this from the answer below?

ScaisEdge Over a year ago

@VijunavVastivch the first just check if the value is not assigned (NULL) the second check if the value is not assigned or the value in as empty string (null or '')

RiggsFolly · Accepted Answer · 2019-02-21 10:06:02Z

1

Try this, it should must work in your case:

SELECT id, CONCAT(first_name, ' ', last_name) as fullname 
FROM YOUR_TABLE_NAME 
WHERE first_name != "" AND last_name != "";

edited Feb 21, 2019 at 10:06

RiggsFolly

94.7k22 gold badges108 silver badges152 bronze badges

answered Feb 21, 2019 at 9:59

Abdul Rehman Sheikh

9399 silver badges18 bronze badges

Collectives™ on Stack Overflow

Select Fields From Table - MYSQL JOIN

4 Answers 4

Comments

1 Comment

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related