In my database, I have 3 tables.
Jokes:
CREATE TABLE IF NOT EXISTS `jokes` (
`joke_id` int(11) NOT NULL AUTO_INCREMENT,
`joke` varchar(1024) NOT NULL,
`category_id` int(11) NOT NULL,
`vote` int(255) NOT NULL,
`date_added` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`joke_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
Category:
CREATE TABLE IF NOT EXISTS `category` (
`category_id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(51) NOT NULL,
PRIMARY KEY (`category_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
And finally, Comments:
CREATE TABLE IF NOT EXISTS `comments` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user` varchar(40) NOT NULL,
`comment` text NOT NULL,
`joke_id` int(11) NOT NULL,
`post_id` int(11) NOT NULL,
`date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
These three tables need to be joined together to get data from each. So far i have used these three queries with no luck in getting what i need.
what i need this query to do is
- Assign the joke_id to the joke_id in the comments, and also displays the joke_id in the view.
- Assign the category_id of a joke to the name and display it
- grab the votes
- grab the jokes
1st query - this query does actually grab all the data i want, but at the same time does not grab the joke_id and pass it to view (as i need the joke_id to assign comments to that unique id):
SELECT j.*, c.name,
co.* FROM jokes j LEFT
JOIN category c ON c.category_id
= j.category_id LEFT JOIN
comments co ON co.joke_id =
j.joke_id WHERE j.joke_id = '$joke_id'
2nd query - This query joins the category to the correct one, and displays the joke and joke_id for me to assign comments to. But it does not show any comments
SELECT j.*, c.name
FROM jokes j LEFT JOIN category c
ON c.category_id = j.category_id
WHERE joke_id = '$joke_id'
3rd query - This query was provided by a user on stack overflow, but seems to throw a tonne of errors my way when a there is no comment attatched to that joke
SELECT c.*, j.*, co.*
FROM jokes j
INNER JOIN category c ON c.category_id = j.category_id
INNER JOIN comments co ON co.joke_id = j.joke_id
WHERE j.joke_id = '$joke_id'
Any help altering this query to get all the items in the three database together would be much appreciated!