CODEWITHSUNDEEP
mysqldatabasejoin
Rehmat Sayany
Rehmat Sayany

Reputation: 61

Show duplicate row in single row mysql

I have two tables. One is TblPeople(ID(PK),firstname,lastname,Embo.. ) and other is TBLembolization(EmbolizationID(PK),PeopleID(FK),EmbolizationDate,.

Here is my query to search people with embolization date by using name...

        SELECT tblpeople.FirstName,details.peopleID,details.embolizationDate FROM tblpeople 
        LEFT JOIN tblembolizations AS details ON  tblpeople.ID=details.peopleID 
        WHERE tblpeople.Embo = 1 and concat(FirstName,' ',LastName) like '%alina%'
        UNION
        SELECT tblpeople.FirstName,details.peopleID,details.embolizationDate FROM tblpeople 
        LEFT JOIN tblembolizations AS details ON  tblpeople.ID=details.peopleID 
        WHERE tblpeople.Embo != 1 and concat(' ',FirstName,' ',LastName,' ') like '%alina%'

The result is:

    ............Result .......................
    FirstName |peopleID|embolizationDate
    Alina     |16,459  |2017-27-03
    Alina     | 16,459 |2017-24-03
    Alina D.  | 14,499 |2017-27-09
    ======================================

You can see that alina has multiple record as she has done embolization on two dates . i want it in single record if someone has done embolization more than 1 time so i want it some thing like this

   ............Result .......................
    FirstName |peopleID|embolizationDate|2ndembolization
    Alina     |16,459  |2017-27-03       |2017-24-03
    Alina D.  | 14,499 |2017-27-09
    ======================================

is it possible to get the answer in single record if someone has done embolization more than once and if some one has done embolization only once than the records are showing in right manner. Thanks

Upvotes: 1

Views: 59

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

I don't understand why you are using UNION. This seems like a simple aggregation.

Putting values in separate columns is tricky. Putting them in a delimited string is easier. Perhaps this does what you want:

SELECT p.FirstName, p.ID, GROUP_CONCAT(d.embolizationDate) as dates
FROM tblpeople p LEFT JOIN
     tblembolizations details
     ON p.ID = details.peopleID 
WHERE pconcat(FirstName,' ',LastName) like '%alina%'
GROUP BY p.FirstName, p.ID;

Upvotes: 2

Related Questions