0

I want to group all pharmacies present in the same night in a unique row following this night.

select days.day, pharmacy.name
from days,
     pharmacy,
     pharmacy_night
where (pharmacy.Id_pharmacy in (SELECT Id_pharmacy
                                FROM pharmacy_in_region(@idregion))
  and pharmacy.Id_pharmacy = pharmacy_night.Id_pharmacy
  and days.Id_day = pharmacy_night.Id_day)

The desired output:

row 1: Monday  Pharmacy1_pahrmacy5-
row 2: Tuesday pharmacy9_

2 Answers2

0

Re-wrote with modern explicit JOIN syntax. I guess you want something like MySQL's GROUP_CONCAT, or similar product specific function. (No dbms specified...)

select days.day, GROUP_CONCAT(pharmacy.name)
from days
  join pharmacy_night on days.Id_day = pharmacy_night.Id_day
  join pharmacy on pharmacy.Id_pharmacy = pharmacy_night.Id_pharmacy
where pharmacy.Id_pharmacy in (SELECT Id_pharmacy
                               FROM pharmacy_in_region(@idregion))
group by days.day
jarlh
  • 42,561
  • 8
  • 45
  • 63
0
SELECT days.day
     , (STUFF((SELECT CAST(', ' + pharmacy.name AS VARCHAR(MAX)) 
         FROM pharmacy 
         WHERE (pharmacy.Id_pharmacy in (SELECT Id_pharmacy
                                FROM pharmacy_in_region(@idregion))
  and pharmacy.Id_pharmacy = pharmacy_night.Id_pharmacy
  and days.Id_day = pharmacy_night.Id_day)
         FOR XML PATH ('')), 1, 2, '')) AS Locations
FROM days,
     pharmacy_night

try stuff + xml path if it is sql server

Sachu
  • 7,555
  • 7
  • 55
  • 94
  • what about this error:The object reference [dbo].[Pharmacy] differs only by case from the object definition [dbo].[pharmacy]. –  May 17 '15 at 08:14
  • @GhadirAyache try the edited one..i put the table name pharmacy again at last `From` class..now i removed..i didnt tested this code..try and tell me – Sachu May 17 '15 at 08:17
  • @GhadirAyache check the error buddy..one Pharmacy has upper case P and second has small one..try to change it in code and check the result – Sachu May 17 '15 at 08:26
  • @GhadirAyache what u got? – Sachu May 17 '15 at 08:36