Hi
I am trying to do a query and it is not working.
The sql fiddle is here: http://www.sqlfiddle.com/#!9/b7a4514/1
CREATE TABLE users (
id int(11) NOT NULL AUTO_INCREMENT,
first_name varchar(255) COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB
INSERT INTO users VALUES (1,‘admin’);
CREATE TABLE user_category_subjects (
id int(11) NOT NULL AUTO_INCREMENT,
user_id int(11) DEFAULT NULL,
category varchar(500) COLLATE utf8_unicode_ci DEFAULT NULL,
subject varchar(500) COLLATE utf8_unicode_ci DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB
INSERT INTO user_category_subjects VALUES (1,1,‘computer science’,‘Programming Languages’), (2,1,‘natural sciences’,‘Physics’),(3,1,‘business’,‘Accounting’);
CREATE TABLE user_items (
id int(11) NOT NULL AUTO_INCREMENT,
user_category_subject_id int(11) DEFAULT NULL,
item varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB;
INSERT INTO user_items VALUES (1,1,‘php’),(2,1,‘javascript’),(3,1,‘c’),(4,1,‘swift’),(5,2,‘astrophycis’),(6,2,‘terrestrial physics’);
Each user can choose more than one user_category_subjects and each of those can in turn have multiple items associated with user_category_subjects.
I am trying to produce one line by user_category_subjects that displays the items associated with each user_category_subject.
I woudl also like to further group that by user_id, but I think if I can do the first, I will be ok with the second.
My query thus far is:
SELECT
q.user_category_subjects_id
, q.category
, q.subject
, q.item_types
FROM (
SELECT user_category_subjects.id AS user_category_subjects_id
, user_category_subjects.category AS category
, user_category_subjects.subject AS subject
, GROUP_CONCAT(user_items.item ORDER BY user_items.item) AS item_types
FROM user_category_subjects
LEFT JOIN user_items
ON user_items.user_category_subject_id = user_category_subjects.id
) AS q;