I have the following JOIN I am attempting to create.
SELECT rating_draft_result.COUNT(id), rating_draft_result.user_id, rating_draft_result.result
FROM rating_draft_result
INNER JOIN users ON
rating_draft_result.user_id, rating_draft_result.result = users.id
I am trying to COUNT the total id's of the rating_draft_result table and then also SELECT the user_id and result from that table. Then I am trying to match the user_id and result from the rating_draft_result table to the user's id column. Then I want to get the username from the user's table where the id matches, but I can't figure that part out and I am getting an error anyways with the following code.
I get the following error..
Survey SELECT total count prepare() failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.id' at line 4
My database tables look like this...
users
CREATE TABLE `users` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`firstname` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
`lastname` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
`email` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
`phone_number` varchar(15) COLLATE utf8_unicode_ci NOT NULL,
`username` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
`password` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
`salt` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
`joined` datetime NOT NULL,
`group` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=105 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
rating_draft_result
CREATE TABLE `rating_draft_result` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL,
`result` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=28 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
What am I doing wrong in my query?
DESIRED OUTPUT:
users CREATE TABLE users ( id``firstname``lastname``email``phone_number``username``password``salt``joined group
1 Jack Johnson jack@email.com 2222 jackusername ffd fdddfd today 1
10 Tom Thompson fdfdfddf@fef.com 5555 Tomusername
rating_draft_result
`id` `user_id` `result`
20 1 10
I want to be able to match the rating_draft_result's user_id and result with the user table's id field.
So I want to get the jackusername and tomusername fields.
Check JOIN condition
SELECT rating_draft_result.user_id, rating_draft_result.result, COUNT(*)
FROM rating_draft_result
INNER JOIN users
ON rating_draft_result.user_id = users.id
GROUP BY rating_draft_result.user_id, rating_draft_result.result;
You have
ON rating_draft_result.user_id, rating_draft_result.result = users.id
The problem is in your JOIN ON clause. You need to change it like
INNER JOIN users ON
rating_draft_result.user_id = users.id
(OR) If you want to have both the conditions then use a AND operator like
FROM rating_draft_result
INNER JOIN users ON
rating_draft_result.user_id = users.id
AND rating_draft_result.result = users.id
Change your entire query to be like below
SELECT COUNT(rating_draft_result.id),
rating_draft_result.user_id,
rating_draft_result.result
FROM rating_draft_result
INNER JOIN users ON
rating_draft_result.user_id = users.id
AND rating_draft_result.result = users.id;