Sql customer with all products query

Question

I have 3 tables

Customers( cid,name,address)
Newspaper( nid, name , price)
Weeklymag ( wid, name , price)
Orders( id , cid , nid, wid)

Now one customer has more than two newspaper or magazine . I am looking for query for following result

john--news1,news2,weeklymag1

King--news1

Jessel--news2,mag1,weeklymag2

Smith--new4,mag3

Or do I need procedure to create this report ??

Thanks for your time and answers .

kjmerf · Answer 1 · 2017-03-02T16:07:46.707

0

You could do this using GROUP CONCAT:

SELECT c.cid, c.name, CONCAT(GROUP_CONCAT(n.name), ', ', GROUP_CONCAT(w.name))
FROM customers c
LEFT JOIN orders o ON c.cid = o.cid
LEFT JOIN newspaper n ON o.nid = n.nid
LEFT JOIN weeklymag w ON o.wid = w.wid
GROUP BY c.cid, c.name

edited Mar 02 '17 at 16:07

answered Mar 02 '17 at 15:18

kjmerf

4,275
3
21
29

I am using my sql server and group_concat doesn't work I am still trying to find out other alternative – DIXIT Mar 02 '17 at 21:31
Thanks I found the substitute of group_concat() by using STUFF() .solved it – DIXIT Mar 03 '17 at 13:05

score 0 · Answer 2 · answered Mar 02 '17 at 15:26

0

Use the GROUP_CONCAT FUNCTION

SELECT c.name, CONCAT(GROUP_CONCAT(n.name SEPARATOR ','), IF(COUNT(DISTINCT n.id)>0 AND COUNT(DISTINCT m.id) >0,',',''), GROUP_CONCAT(m.name SEPARATOR ',')) mags
FROM customers c
LEFT JOIN orders o ON o.cid = c.cid
LEFT JOIN newspaper n ON n.nid = o.nid
LEFT JOIN weeklymag m ON m.wid = o.wid
GROUP BY c.cid

answered Mar 02 '17 at 15:26

user1898027

330
2
13

I am using sql server and group_concat does not work any other alternative – DIXIT Mar 02 '17 at 21:34
Ahh okay, I don't have much expierence with SQL server, but here it seems like somebody solved a similar question [link](http://stackoverflow.com/questions/17591490/how-to-make-a-query-with-group-concat-in-sql-server) – user1898027 Mar 03 '17 at 08:16
Thanks I got it . – DIXIT Mar 03 '17 at 13:05

score 0 · Answer 3 · answered Mar 03 '17 at 13:26

SELECT C.NAME ,

    STUFF((SELECT DISTINCT' '+ N.NAME + ' | '
                FROM NEWSPAPER N   
                JOIN ORDERS O ON O.NEWS_ID = N.ID 
                WHERE O.CUST_ID = C.ID
                FOR XML PATH('') , TYPE).value('.','NVARCHAR(MAX)'),1,0,'')as NEWS
     ,
     STUFF((SELECT DISTINCT' '+ w.name +' | '
                FROM weekly w   
                JOIN ORDERS O ON O.Weekly_id = w.id  
                WHERE O.CUST_ID = C.ID
                FOR XML PATH('') , TYPE).value('.','NVARCHAR(MAX)'),1,0,'')as WEEKLY
    ,STUFF((SELECT DISTINCT' '+ m.name +' | '
                FROM monthly m   
                JOIN ORDERS O ON o.monthly_id  = m.id  
                WHERE O.CUST_ID = C.ID
                FOR XML PATH('') , TYPE).value('.','NVARCHAR(MAX)'),1,0,'')as MAG

FROM CUSTOMER C

Sql customer with all products query

3 Answers3