Mysql Query for a Forum categories/topics The Next CEO of Stack OverflowHow to output MySQL query results in CSV format?Should I use the datetime or timestamp data type in MySQL?How to get a list of MySQL user accountsHow do I get the latest post for each category in this forum using MySQL?How to reset AUTO_INCREMENT in MySQL?query for selectiong parents and childHow to import an SQL file using the command line in MySQL?Write one SQL query for multiple valuesSelect latest topic from a category (SQL and PHP)MySQL, query two tables and check if photos are assigned to category
Should I tutor a student who I know has cheated on their homework?
Easy to read palindrome checker
Why does standard notation not preserve intervals (visually)
Running a General Election and the European Elections together
"misplaced omit" error when >centering columns
Prepend last line of stdin to entire stdin
How many extra stops do monopods offer for tele photographs?
Is it my responsibility to learn a new technology in my own time my employer wants to implement?
What did we know about the Kessel run before the prequels?
A Man With a Stainless Steel Endoskeleton (like The Terminator) Fighting Cloaked Aliens Only He Can See
Some questions about different axiomatic systems for neighbourhoods
Make solar eclipses exceedingly rare, but still have new moons
Do I need to write [sic] when a number is less than 10 but isn't written out?
Why did CATV standarize in 75 ohms and everyone else in 50?
Does Germany produce more waste than the US?
Is it professional to write unrelated content in an almost-empty email?
Does increasing your ability score affect your main stat?
What connection does MS Office have to Netscape Navigator?
Won the lottery - how do I keep the money?
Example of a Mathematician/Physicist whose Other Publications during their PhD eclipsed their PhD Thesis
What was the first Unix version to run on a microcomputer?
How to get from Geneva Airport to Metabief?
Why isn't the Mueller report being released completely and unredacted?
Method for adding error messages to a dictionary given a key
Mysql Query for a Forum categories/topics
The Next CEO of Stack OverflowHow to output MySQL query results in CSV format?Should I use the datetime or timestamp data type in MySQL?How to get a list of MySQL user accountsHow do I get the latest post for each category in this forum using MySQL?How to reset AUTO_INCREMENT in MySQL?query for selectiong parents and childHow to import an SQL file using the command line in MySQL?Write one SQL query for multiple valuesSelect latest topic from a category (SQL and PHP)MySQL, query two tables and check if photos are assigned to category
i am trying to code a forum website where i want to show in the homepage all the categories and, for each category, the last topic if it exists(if it doesn't exist i put a button that says "create a topic").
The problem is that i am pretty new to the sql world and i can't figure out the query that i need.
so my tables are:
Category table
and : Topics table
so i need a table like this: Result table needed
columns that connect the two tables are cat_id in Categories, and topic_cat in Topics.
i tried this query:
select cat_name,topic_cat, max(topic_date) AS data from topics group by topic_cat
but i don't know how to continue.
where for each category i join only the latest topic(topic_date) and where the topic doesn't exist, fiels are null.
Thanks in advance.
(sorry for my bad english :/)
mysql
asked Mar 7 at 16:32
AlessandroAlessandro
32
add a comment |
i am trying to code a forum website where i want to show in the homepage all the categories and, for each category, the last topic if it exists(if it doesn't exist i put a button that says "create a topic").
The problem is that i am pretty new to the sql world and i can't figure out the query that i need.
so my tables are:
Category table
and : Topics table
so i need a table like this: Result table needed
columns that connect the two tables are cat_id in Categories, and topic_cat in Topics.
i tried this query:
select cat_name,topic_cat, max(topic_date) AS data from topics group by topic_cat
but i don't know how to continue.
where for each category i join only the latest topic(topic_date) and where the topic doesn't exist, fiels are null.
Thanks in advance.
(sorry for my bad english :/)
mysql
asked Mar 7 at 16:32
AlessandroAlessandro
32
Hello Alessandro. Your english is pretty good. But please provide some code for what you have tried so far.
– Olafant
Mar 7 at 16:39
add a comment |
i am trying to code a forum website where i want to show in the homepage all the categories and, for each category, the last topic if it exists(if it doesn't exist i put a button that says "create a topic").
The problem is that i am pretty new to the sql world and i can't figure out the query that i need.
so my tables are:
Category table
and : Topics table
so i need a table like this: Result table needed
columns that connect the two tables are cat_id in Categories, and topic_cat in Topics.
i tried this query:
select cat_name,topic_cat, max(topic_date) AS data from topics group by topic_cat
but i don't know how to continue.
where for each category i join only the latest topic(topic_date) and where the topic doesn't exist, fiels are null.
Thanks in advance.
(sorry for my bad english :/)
mysql
asked Mar 7 at 16:32
AlessandroAlessandro
32
i am trying to code a forum website where i want to show in the homepage all the categories and, for each category, the last topic if it exists(if it doesn't exist i put a button that says "create a topic").
The problem is that i am pretty new to the sql world and i can't figure out the query that i need.
so my tables are:
Category table
and : Topics table
so i need a table like this: Result table needed
columns that connect the two tables are cat_id in Categories, and topic_cat in Topics.
i tried this query:
select cat_name,topic_cat, max(topic_date) AS data from topics group by topic_cat
but i don't know how to continue.
where for each category i join only the latest topic(topic_date) and where the topic doesn't exist, fiels are null.
Thanks in advance.
(sorry for my bad english :/)
mysql
mysql
asked Mar 7 at 16:32
AlessandroAlessandro
32
asked Mar 7 at 16:32
AlessandroAlessandro
32
edited Mar 7 at 17:29
Alessandro
asked Mar 7 at 16:32
AlessandroAlessandro
32
asked Mar 7 at 16:32
AlessandroAlessandro
32
asked Mar 7 at 16:32
AlessandroAlessandro
32
32
Hello Alessandro. Your english is pretty good. But please provide some code for what you have tried so far.
– Olafant
Mar 7 at 16:39
add a comment |
Hello Alessandro. Your english is pretty good. But please provide some code for what you have tried so far.
– Olafant
Mar 7 at 16:39
Hello Alessandro. Your english is pretty good. But please provide some code for what you have tried so far.
– Olafant
Mar 7 at 16:39
Hello Alessandro. Your english is pretty good. But please provide some code for what you have tried so far.
– Olafant
Mar 7 at 16:39
add a comment |
1 Answer
1
active
oldest
votes
It seems there is no link between the category and topic tables.
You need to set a foreign key :
E.g catergory_id in the topic table or a topic_id in the category table
depending on what table should hold the relation
Or this could be a join table...
You should check some tutorial like this one to understand this : http://www.sql-join.com/
answered Mar 7 at 16:44
MattOverF.MattOverF.
565
Thanks for the answer, actually the columns that connect the two tables are cat_id in Categories, and topic_cat in Topics. I didn't specify it, sorry.
– Alessandro
Mar 7 at 17:01
Try something like this:select * from category c left join topics t on c.cat_id = t.topic_cat AND t.post_date = (SELECT MAX(post_date) FROM topics WHERE topic_cat = t.topic_cat)Left join is what you shoud use if you want all the records from a table (category) even if they have no corresponding entries in the related table (topics)
– MattOverF.
Mar 8 at 6:58
Thanks alot man, works like a charm, that's exactly what i wanted!
– Alessandro
Mar 8 at 9:15
add a comment |
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55048654%2fmysql-query-for-a-forum-categories-topics%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
It seems there is no link between the category and topic tables.
You need to set a foreign key :
E.g catergory_id in the topic table or a topic_id in the category table
depending on what table should hold the relation
Or this could be a join table...
You should check some tutorial like this one to understand this : http://www.sql-join.com/
answered Mar 7 at 16:44
MattOverF.MattOverF.
565
Thanks for the answer, actually the columns that connect the two tables are cat_id in Categories, and topic_cat in Topics. I didn't specify it, sorry.
– Alessandro
Mar 7 at 17:01
Try something like this:select * from category c left join topics t on c.cat_id = t.topic_cat AND t.post_date = (SELECT MAX(post_date) FROM topics WHERE topic_cat = t.topic_cat)Left join is what you shoud use if you want all the records from a table (category) even if they have no corresponding entries in the related table (topics)
– MattOverF.
Mar 8 at 6:58
Thanks alot man, works like a charm, that's exactly what i wanted!
– Alessandro
Mar 8 at 9:15
add a comment |
It seems there is no link between the category and topic tables.
You need to set a foreign key :
E.g catergory_id in the topic table or a topic_id in the category table
depending on what table should hold the relation
Or this could be a join table...
You should check some tutorial like this one to understand this : http://www.sql-join.com/
answered Mar 7 at 16:44
MattOverF.MattOverF.
565
Thanks for the answer, actually the columns that connect the two tables are cat_id in Categories, and topic_cat in Topics. I didn't specify it, sorry.
– Alessandro
Mar 7 at 17:01
Try something like this:select * from category c left join topics t on c.cat_id = t.topic_cat AND t.post_date = (SELECT MAX(post_date) FROM topics WHERE topic_cat = t.topic_cat)Left join is what you shoud use if you want all the records from a table (category) even if they have no corresponding entries in the related table (topics)
– MattOverF.
Mar 8 at 6:58
Thanks alot man, works like a charm, that's exactly what i wanted!
– Alessandro
Mar 8 at 9:15
add a comment |
It seems there is no link between the category and topic tables.
You need to set a foreign key :
E.g catergory_id in the topic table or a topic_id in the category table
depending on what table should hold the relation
Or this could be a join table...
You should check some tutorial like this one to understand this : http://www.sql-join.com/
answered Mar 7 at 16:44
MattOverF.MattOverF.
565
It seems there is no link between the category and topic tables.
You need to set a foreign key :
E.g catergory_id in the topic table or a topic_id in the category table
depending on what table should hold the relation
Or this could be a join table...
You should check some tutorial like this one to understand this : http://www.sql-join.com/
answered Mar 7 at 16:44
MattOverF.MattOverF.
565
answered Mar 7 at 16:44
MattOverF.MattOverF.
565
answered Mar 7 at 16:44
MattOverF.MattOverF.
565
answered Mar 7 at 16:44
MattOverF.MattOverF.
565
565
Thanks for the answer, actually the columns that connect the two tables are cat_id in Categories, and topic_cat in Topics. I didn't specify it, sorry.
– Alessandro
Mar 7 at 17:01
Try something like this:select * from category c left join topics t on c.cat_id = t.topic_cat AND t.post_date = (SELECT MAX(post_date) FROM topics WHERE topic_cat = t.topic_cat)Left join is what you shoud use if you want all the records from a table (category) even if they have no corresponding entries in the related table (topics)
– MattOverF.
Mar 8 at 6:58
Thanks alot man, works like a charm, that's exactly what i wanted!
– Alessandro
Mar 8 at 9:15
add a comment |
Thanks for the answer, actually the columns that connect the two tables are cat_id in Categories, and topic_cat in Topics. I didn't specify it, sorry.
– Alessandro
Mar 7 at 17:01
Try something like this:select * from category c left join topics t on c.cat_id = t.topic_cat AND t.post_date = (SELECT MAX(post_date) FROM topics WHERE topic_cat = t.topic_cat)Left join is what you shoud use if you want all the records from a table (category) even if they have no corresponding entries in the related table (topics)
– MattOverF.
Mar 8 at 6:58
Thanks alot man, works like a charm, that's exactly what i wanted!
– Alessandro
Mar 8 at 9:15
Thanks for the answer, actually the columns that connect the two tables are cat_id in Categories, and topic_cat in Topics. I didn't specify it, sorry.
– Alessandro
Mar 7 at 17:01
Thanks for the answer, actually the columns that connect the two tables are cat_id in Categories, and topic_cat in Topics. I didn't specify it, sorry.
– Alessandro
Mar 7 at 17:01
Try something like this:
select * from category c left join topics t on c.cat_id = t.topic_cat AND t.post_date = (SELECT MAX(post_date) FROM topics WHERE topic_cat = t.topic_cat) Left join is what you shoud use if you want all the records from a table (category) even if they have no corresponding entries in the related table (topics)– MattOverF.
Mar 8 at 6:58
Try something like this:
select * from category c left join topics t on c.cat_id = t.topic_cat AND t.post_date = (SELECT MAX(post_date) FROM topics WHERE topic_cat = t.topic_cat) Left join is what you shoud use if you want all the records from a table (category) even if they have no corresponding entries in the related table (topics)– MattOverF.
Mar 8 at 6:58
Thanks alot man, works like a charm, that's exactly what i wanted!
– Alessandro
Mar 8 at 9:15
Thanks alot man, works like a charm, that's exactly what i wanted!
– Alessandro
Mar 8 at 9:15
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55048654%2fmysql-query-for-a-forum-categories-topics%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Hello Alessandro. Your english is pretty good. But please provide some code for what you have tried so far.
– Olafant
Mar 7 at 16:39