hi everyone,
i am working on a project in codeigniter and there i have a search form module in which i have to search the forum Threads with any given number of replies entered through the search form my tables schema is like
1.forum_question
2.forum_replies
3.forum_categories
forum_question -
id--
-
subject--
-
message--
-
category_id--
-
started_by--
-
forum_answer -
id--
-
reply--
-
question_id--
-
sent_from--
-
forum_categories
i have tried it several times but it is not working out as from the search form the user can send any parameters for search and if he chooses to search all posts with maximum / minimum 5 replies or so , it wont be possible with having the forum_question_id
my following query searches the threads that have replies in the forum_answer table -
SELECT q.id,q.subject FROM `bm_forum_question` q where q.id IN (select a.forum_question_id from bm_forum_answer a)
-
This lists out all the threads with their subject and question_id i want to have the count against each thread that shows how many replies are there against each of them how to do that without having any specific question_id.
regards,
Omer Aslam
no rabbit i have done it here is what i was asking you to do, but thanks anyway for your time. -
SELECT q.id, q.subject, count( DISTINCT a.message ) AS count
-
FROM `bm_forum_answer` a
-
JOIN bm_forum_question q ON q.id = a.forum_question_id
-
GROUP BY q.id
-
regards,
Omer Aslam
9  2520 
Join the question table to the answer table and do a count.
And I have no idea what you mean by:
shows how many replies are there against each of them how to do that without having any specific question_id
That doesn't make any sense because you're saying that you want a count of replies by question but you don't want to tie the count to a question.
no what i meant to say is i need to list the all the treads / questions that have any replies against them and i have to show one column that holds the total count of the replies along with the question_id and subject of the question / thread , regardless of depending on any specific question id provided , look at this example -
select count(*) from forum_answer where question_id = '1'
-
this query can count on the total replies against the question that has the id 1 but i need to list all the Threads weather they have a reply or not and i have to list them in front of each thread which should look like this -
id subject Total_Replies
-
1 This is the subject of the thread. 0
-
2 This is the subject of another message. 5
-
3 Dummy subject of another message. 15
-
and i need to do this inside mysql query
hope i made some sense here
regards,
Omer Aslam
My answer from post #2 is still what you want to do.
i need to print the follwoing by query can you -
id subject Total_Replies
-
1 This is the subject of the thread. 0
-
2 This is the subject of another message. 5
-
3 Dummy subject of another message. 15
-
Yes, you already said that. And I've already answered it in post #2. I'm not sure why you're repeating yourself. Are you saying you don't know how to do an aggregate query? If so, then an aggregate query looks like this: - SELECT
-
field1,
-
COUNT(*) AS countRows
-
FROM someTable
-
GROUP BY
-
field1
If you are repeating yourself for some other reason, you need to tell us what that reason is.
On a related side note, in your example, you shouldn't have row 1 in the results, because it has no answers, it shouldn't show up in your results per your stated requirements.
no rabbit i have done it here is what i was asking you to do, but thanks anyway for your time. -
SELECT q.id, q.subject, count( DISTINCT a.message ) AS count
-
FROM `bm_forum_answer` a
-
JOIN bm_forum_question q ON q.id = a.forum_question_id
-
GROUP BY q.id
-
regards,
Omer Aslam
Yes, that's exactly what my suggestion was. I'm not here to do your work for you. I'm here to guide you to the solution. The solution you ended up at was the solution I was suggesting. But I wasn't going to write up the solution for you with exact syntax.
:) i know rabbit, i m here from last 6 years , but i still cant see any suggestions frm you even, other than last post
Yes, you already said that. And I've already answered it in post #2. I'm not sure why you're repeating yourself. Are you saying you don't know how to do an aggregate query? If so, then an aggregate query looks like this:
Expand|Select|Wrap|Line Numbers
SELECT
field1,
COUNT(*) AS countRows
FROM someTable
GROUP BY
field1
If you are repeating yourself for some other reason, you need to tell us what that reason is.
On a related side note, in your example, you shouldn't have row 1 in the results, because it has no answers, it shouldn't show up in your results per your stated requirements.
thanks a lot any way
Well, if you look at the first line of post #2, I said:
Join the question table to the answer table and do a count.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Kishor |
last post by:
Hi Friends
Please help me to write a C program to find the 5th (fifth) root of a given number.
Ex:(1) Input : 32
Output : 5th root of 32 is 2
Ex:(1) Input : 243
Output : 5th root of 243 is...
|
by: Reddy |
last post by:
Hi,
How to check whether a given number is an integer
Thanks,
Reddy
|
by: gchiazx |
last post by:
Hi, can someone send me the algorithm for finding the products of prime numbers from a given number?
Thank You
Gary.
|
by: scan87 |
last post by:
Can somone please, please give me the solution for the following problem. I need to submit it on Monday.
Write a global function called format, which formats numbers with a given number of decimal...
|
by: psbasha |
last post by:
Hi,
How to find the number of digits from a given number?.
For example
num = 23456
The number of digits are 5
num = 2311150
|
by: danner |
last post by:
I am using Visual Basic 2005 Express Edition. I have used it to create the database.. SQL Server Database file CodeDatabase.mdf
My experience is outdated.. from QBasic over a decade ago. I am...
|
by: stalin |
last post by:
hi
i have one problem suppose if we given a number 163 as
input the out put of the number is
one --100
one--- 50
one---10
one-- 2
one--1
|
by: gaurav1983 |
last post by:
i have to generate unique combinations of given number of digits entered by user
eg: N=4 (0,1,2,3)
output should be
0
1
2
3
|
by: shivpratap |
last post by:
I m solving a problem to find the sum of divisors of a given number(the number is a large one),...i hv written the code bt it's exceding the time limit...hw can i reduce the time in such cases...pls...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
| |
