I am setting up a database view which will be called from a web application. Having trouble construct this query.
The database backend is on MySQL and site is being developed in PHP. I've tried a combination of joins but still don't seem to be getting the results I need. Frustrated.
Assume I have the following tables:
CREATE TABLE #tmp_employees
(
name VARCHAR(30),
age INT
)
insert into #tmp_employees
(name, age)
values
('BM', 45),
('JL', 44),
('DH', 46);
CREATE TABLE #tmp_sites
(
name VARCHAR(30),
social_type VARCHAR(30),
site VARCHAR(100)
)
insert into #tmp_sites
(name, social_type, site)
values
('BM', 'instagram', 'instangramlink/site1'),
('BM', 'instagram', 'instangramlink/site2'),
('BM', 'instagram', 'instangramlink/site3'),
('BM', 'twitter', 'twitterlink/site1'),
('JL', 'twitter', 'twitterlink/site1a');
CREATE TABLE #tmp_site_type
(site VARCHAR(30))
insert into #tmp_site_type
(site )
values
('instagram'),
('twitter'),
('snapchat');
I am trying to get a query result in this format.
The site_types are pivoted with a listing of the Name and social links for each site_type.
NAME; AGE; INSTAGRAM; TWITTER; SNAPCHAT
BM; 45;instangramlink/site1, instangramlink/site2, instangramlink/site3;twitterlink/site1;NULL
JL; 44;NULL;twitterlink/site1a;NULL
DH;46;NULL;NULL;NULL
Thank you in advance.