5 Replies - 6153 Views - Last Post: 26 February 2014 - 05:47 AM

#1 CY5   User is offline

  • D.I.C Regular

Reputation: 17
  • View blog
  • Posts: 413
  • Joined: 28-September 12

How to merge two column into one of different tables

Posted 25 February 2014 - 12:39 PM

I wanted to merge two Columns of different table into one(Certification of Instructor table and Typing_Speed of Receptionist table)
mysql> Select Employee.E_ID,Employee.Fname,Employee.Mname,Employee.Lname,Employee.Address,Employee.Contact,Instructor.Certification+Receptionist.Typing_Speed as Skill 
       FROM 
Employee 
LEFT JOIN Instructor ON Employee.E_ID=Instructor.Inst_ID 
LEFT JOIN Receptionist ON Employee.E_ID=Receptionist.R_ID;


and i get SKILL Column as all NULL, but i have value in Typing_Speed as well as in Certification

This post has been edited by CY5: 25 February 2014 - 12:52 PM


Is This A Good Question/Topic? 0
  • +

Replies To: How to merge two column into one of different tables

#2 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 14761
  • View blog
  • Posts: 59,010
  • Joined: 12-June 08

Re: How to merge two column into one of different tables

Posted 25 February 2014 - 12:42 PM

That doesn't seem to make much sense combining them together. Why would two different job roles have random data merged? Typically one would do this on the display side.

Perhaps your data types are off, or confusing what is there. Perhaps try concat.

https://dev.mysql.co...function_concat
https://dev.mysql.co...ction_concat-ws
Was This Post Helpful? 0
  • +
  • -

#3 CY5   User is offline

  • D.I.C Regular

Reputation: 17
  • View blog
  • Posts: 413
  • Joined: 28-September 12

Re: How to merge two column into one of different tables

Posted 25 February 2014 - 12:56 PM

Quote

That doesn't seem to make much sense combining them together. Why would two different job roles have random data merged? Typically one would do this on the display side

Employee can have only one skill either Typing_Speed or Course Certificate


I tried using Concat still getting Skill as NULL
mysql> Select Employee.E_ID,Employee.Fname,Employee.Mname,Employee.Lname,Employe
e.Address,Employee.Contact,CONCAT(Instructor.Certification,Receptionist.Typing_s
peed)as Skill
    ->        FROM
    -> Employee
    -> LEFT JOIN Instructor ON Employee.E_ID=Instructor.Inst_ID
    -> LEFT JOIN Receptionist ON Employee.E_ID=Receptionist.R_ID;



@modi123_1 thanks for tutorial, but if you can provide tutorial on Merging two Columns of different table into one will much helpful.

This post has been edited by CY5: 25 February 2014 - 12:59 PM

Was This Post Helpful? 0
  • +
  • -

#4 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 14761
  • View blog
  • Posts: 59,010
  • Joined: 12-June 08

Re: How to merge two column into one of different tables

Posted 25 February 2014 - 01:09 PM

Then your data is having issues. Are you certain your keys are right? How about some sample data there.

Quote

@modi123_1 thanks for tutorial, but if you can provide tutorial on Merging two Columns of different table into one will much helpful.

That is what concat does.
Was This Post Helpful? 0
  • +
  • -

#5 CY5   User is offline

  • D.I.C Regular

Reputation: 17
  • View blog
  • Posts: 413
  • Joined: 28-September 12

Re: How to merge two column into one of different tables

Posted 26 February 2014 - 04:33 AM

I have created Employee, Receptionist, Instructor inSQLFIDDLE.
Was This Post Helpful? 0
  • +
  • -

#6 CY5   User is offline

  • D.I.C Regular

Reputation: 17
  • View blog
  • Posts: 413
  • Joined: 28-September 12

Re: How to merge two column into one of different tables

Posted 26 February 2014 - 05:47 AM

@modi123_1: Problem is solved, thanks for help, I have not used concat but instead i have use INNER JOIN and UNION, the query is big, if these can be shortened would be much helpful. Demo in SQL FIDDLE

Solution:
 Select Employee.E_ID, Employee.Fname, Employee.Mname, Employee.Lname, Employee.Address,
 Employee.Contact,Instructor.Certification as Skill
    From Employee
      INNER JOIN Instructor
        ON Employee.E_ID=Instructor.Inst_ID
  UNION
  Select Employee.E_ID, Employee.Fname, Employee.Mname, Employee.Lname, Employee.Address,
   Employee.Contact,Receptionist.Typing_Speed as Skill
    From Employee
      INNER JOIN Receptionist
        ON Employee.E_ID=Receptionist.R_ID;


Was This Post Helpful? 0
  • +
  • -

Page 1 of 1