How to concatenate three tables into one table?

Question

Table1 tblUserBadges

uid scenid  badgeid timestamp

u0    s1      b0       t1

u0    s1      b1       t2

u0    s2      b0       t3

u1    s1      b3       t4

u1    s1      b4       t5   

Table2 tblUserWarnings

uid scenid  warningid   timestamp

u0    s1      w0         t1

u0    s1      w1         t2

u0    s2      w2         t3

u1    s1      w3         t4

u1   s1       w4         t5

Table3 tblUserScenScores

uid scenid  score   attempts    timestamp


u0    s1     20       3            t1


u1    s1     22       7            t2

u0    s2     -5       1            t3

RESULT : Tabl4 viewUserScenarioStats

uid scenid  badges   warnings   score   attempts
u0   s1     b0, b1   w0, w1      20       3

u0   s2     b0       w2          -5       1

u1   s1     b3, b4   w3, w4      22       7

I tried query as follow:

CREATE VIEW viewUserScenarioStats AS 
SELECT  uid.tblUserBadge, GROUP_CONCAT(scenid).tblUserWarnings, 
GROUP_CONCAT( badgeid).tblUserScrores, warningid.tblUserWarnings, score, attempts 

How to concatenate three tables. But not working.....Any help?

Answer 1

You can do so

select s.uid ,
s.scenid,
group_concat(distinct b.badgeid) badges,
group_concat(distinct w.warningid)  `warnings`,
s.score,
s.attempts
from tblUserScenScores s
join tblUserWarnings w on(s.uid = w.uid and s.scenid = w.scenid)
join tblUserBadges b  on(s.uid = b.uid and s.scenid = b.scenid)
group by s.uid ,s.scenid

DEMO

View Demo