Research Development Research Development - 4 months ago 11
SQL Question

How to add three row and keep in third column row with Join in mySql

Table1:

CREATE TABLE Persons
(
PersonID int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);

INSERT INTO Persons VALUES (1, 'Kumar', 'Anil', 'JP NAGAR','BANGLORE');
INSERT INTO Persons VALUES (2, 'Kumar', 'Sunil', 'LAXMI NAGAR','Delhi');


Data:

Personid LastName irstName Address City
'1' 'Kumar' 'Anil' 'JP NAGAR' 'BANGLORE'
'2' 'Kumar' 'Sunil' 'LAXMI NAGAR' 'Delhi'


Table2:

CREATE TABLE PERSON_DETAIL (
userid int ,
email VARCHAR(80) ,
PersonID int,
displayname VARCHAR(50)
)

INSERT INTO PERSON_DETAIL VALUES (11, 'a@gmail.com', 1, 'AK');
INSERT INTO PERSON_DETAIL VALUES (12, 's@gmail.com', 2, 'SK');


Data:

userid email PersonId displayname
'11' 'a@gmail.com' '1' 'AK'
'12' 's@gmail.com' '2' 'SK'


I want Join Like this so that it Give output like this:

PersonId lastnmae firstname combineddata
1 Kumar Anil a@gmail.com_JpNAGAR_BANGLORE
2 Kumar Sunil s@gmail.com_LaxmiNagar_Delhi


Please help me how to Join so that I give desire output.

I have tried Inner Join But unable to get that Output.

Answer

Simple JOIN with concatenate the strings:

For SQL Server:

SELECT PE.PersonID, 
       PE.Lastname, 
       PE.FirstName,
       PD.email + '_' + PE.Address + '_' + PE.City AS combineddata
FROM Persons PE
JOIN PERSON_DETAIL PD ON PD.PersonID = PE.PersonID

For MySQL, please try with CONCAT

SELECT PE.PersonID, 
       PE.Lastname, 
       PE.FirstName,
       CONCAT(PD.email,  '_' ,  PE.Address , '_' , PE.City) AS combineddata
FROM Persons PE
JOIN PERSON_DETAIL PD ON PD.PersonID = PE.PersonID

Working DEMO