CODEWITHSUNDEEP
phpmysql
php know
php know

Reputation: 1

mysql result set from two tables commas

Have a table patients

pat_id pat_name 1 Raul

Another table with medicines

medicine_id medicine_name pat_id
1           Paracetamol   1
2           Crocin        2

Here i need to get the result set like

pat_id   pat_name   medicine_name
1         Raul      Paracetamol,Crocin

How can i achieve this..

I know that if i joined 2 tables like his 

SELECT * FROM patients LEFT JOIN medicines WHERE pat_id = `1`

I will get 2 rows, but how can i achieve the expected result

Thanks

Upvotes: 0

Views: 40

Answers (1)

marcosh
marcosh

Reputation: 9008

You should use GROUP BY and GROUP_CONCAT, something like

SELECT pat_id, pat_name, GROUP_CONCAT(medicine_name)
FROM patients LEFT JOIN medicines 
WHERE pat_id = `1` GROUP BY pat_id

Upvotes: 3

Related Questions