JulianJ JulianJ - 4 months ago 8
SQL Question

Mysql query across three tables?

I'm building a site that allows users to upload posters of television productions they have made. Other users can add themselves to the posters if they were involved with the production and their names get listed below the poster too.

I am having problems writing a mysql query that will allow me to list all the uploaded posters but also any of the users that have listed themselves as being involved with the production. I have made this sql fiddle that might help. The current query displays all the uploaded posters but not those who have added themselves to the poster. Any ideas?

The query

SELECT tbl_uploads.file_name, tbl_users.user_id, tbl_users.user_name, tbl_collab.collab_userid, tbl_collab.collab_username
FROM tbl_uploads

left join tbl_collab on tbl_collab.file_name = tbl_uploads.file_name

left join tbl_users on tbl_uploads.user_id = tbl_users.user_id
group by tbl_uploads.file_name


The tables

CREATE TABLE IF NOT EXISTS `tbl_users` (
`user_id` int(11) NOT NULL,
`user_name` varchar(25) NOT NULL,
`user_email` varchar(60) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

INSERT INTO `tbl_users` (`user_id`, `user_name`,`user_email`) VALUES
(2, 'julian', 'julian@email.com'),
(3, 'bob', 'bob@email.com'),
(4, 'sue', 'sue@email.com');

CREATE TABLE IF NOT EXISTS `tbl_uploads` (
`id` int(10) NOT NULL,
`file_name` varchar(100) NOT NULL,
`user_id` int(11) NOT NULL
) ENGINE=MyISAM AUTO_INCREMENT=17 DEFAULT CHARSET=latin1;

INSERT INTO `tbl_uploads` (`id`, `file_name`, `user_id`) VALUES
('7', 'Julians Picture','2' ),
('13', 'Julians 2nd picture','2' ),
('14', 'Bobs Picture','3' ),
('15', 'Another Picture','3' );


CREATE TABLE IF NOT EXISTS `tbl_collab` (
`id` int(11) NOT NULL,
`user_id` int(11) NOT NULL,
`collab_username` varchar(255) NOT NULL,
`file_name` varchar(255) NOT NULL,
`collab_userid` varchar(255) NOT NULL
) ENGINE=MyISAM AUTO_INCREMENT=15 DEFAULT CHARSET=latin1;

INSERT INTO `tbl_collab` (`id`,`file_name`,`collab_userid`, `user_id`,`collab_username`) VALUES ('1','Bobs Picture','4','4','Sue' ), ('2','Another Picture','3','3','Bob' )
,('3','Bobs Picture','2','2','Julian' );

Answer

This did what I was looking for. GROUP_CONCAT did the trick

SELECT up.file_name, GROUP_CONCAT(c.collab_username)
FROM tbl_uploads up
LEFT JOIN tbl_users p ON up.user_id = p.user_id
LEFT JOIN tbl_collab c ON up.file_name = c.file_name
GROUP BY up.file_name
Comments