2

I am newbie in writing SQL queries and this is for a mySQL database.

I have a Table called PatientVisit (PV), which has a one to one with BillMaster (BM). Each visit has one bill, which then has a one to many with BillDetail (BD). When i list out the Visit details from PatientVisit (PV), i need to print a string with the set of 'ServiceName' columns associated with that one visit.

So for example, the PatientVisit.ID number '1' has a corresponding BillMaster.Bill No '1' which has 2 entries in BillDetail 'Consultation' and 'Dressing'.

When i print details of Visit 1, i need 'Consultation,Dressing' as one string value for the 'Service Name' column.

If i had a one to one , then the query would have been simple as follow :

select PV.ID, BM.BillNo,  BD.ServiceName 
FROM PatientVisits PV INNER JOIN BillMaster BM ON BM.VisitID = PV.ID 
INNER JOIN BillDetail BD ON BD.BillNo = BM.BillNo
WHERE ....

However, since it is one to many for the ServiceName column, how can this query be written ?

Thanks, Chak.

Mark Byers
  • 811,555
  • 193
  • 1,581
  • 1,452
Chakra
  • 2,525
  • 8
  • 43
  • 82
  • 4
    http://stackoverflow.com/questions/149772/how-to-use-group-by-to-concatenate-strings-in-mysql http://stackoverflow.com/questions/276927/can-i-concatenate-multiple-mysql-rows-into-one-field – nickf Jul 21 '10 at 11:17

1 Answers1

0

Try this 

select PV.ID, BM.BillNo, 
    GROUP_CONCAT(BD.ServiceName)
    FROM PatientVisits PV INNER JOIN BillMaster BM ON BM.VisitID = PV.ID 
    INNER JOIN BillDetail BD ON BD.BillNo = BM.BillNo
    WHERE ..
    GROUP BY PV.ID,BM.BillNo

..

MKumar
  • 1,524
  • 1
  • 14
  • 22
  • I think i also have to include BD.ServiceCode in the GroupBy, which i did and it worked out. – Chakra Jul 23 '10 at 03:46