MYSQL :: WHERE COUNT MySQL Query

MySQL Query COUNT
I need to create a sql statement that queries a mysql database for the top x amount of entries that include their name. Basically the site logs everytime they upload a file, and I want to see which users have the top number of uploads. Any ideas? Thanks in advanced to any help.

Table:

fileid
filename
submittername
submitterip
fileinfo
filetype
date

How To Improve The Speed Of Mysql Query Using Count(*)
I'm using this kind of queries in mysql in InnoDB engine

Select count(*) from marking1 where persondate between '2007-04-23 00:00:00.000' and '2007-04-23 23:59:59.999' and PersonName='aaa'

While executing these queries from front end VB, It takes above 5 secs with 50 thousand records.

How can I improve speed for this kind of queries. Is there any alternation for this command.

Select Query With Sub-query For Count And Max
I have a table "test"... as below... I want to have a result set of all the parents with the count of their children and the lastest created date of their child ...

SQL Count Query
I have 3 tables:

j_photoparent (holds photo "parent" e..g "nature", "places")
j_photocat (holds photo categories, e.g. "leaves", "snow", "london eye")
j_photosmaster holds photo details

The keys are as follows:

j_photoparent.parentID
j_photocat.catID
j_photocat.parentID
j_photosmaster.photoID
j_photosmaster.catID

I have a simple SQL statement to randomly select a photo category:

SELECT jpc.catID, jpp.parentID
FROM j_photocat jpc, j_photoparent jpp
WHERE jpp.parentID = jpc.parentID
ORDER BY RAND()
LIMIT 1
What would be great though, would be if I could combine the SQL above, to also count how many photos in the "j_photosmaster" table for each randomly selected category.

Could it be done in the same statement - or would I need to do two - the first one above and then this one:

SELECT COUNT(*) FROM j_photosmaster
WHERE CatID = &catID_FROM_ABOVE
Could it be done with a sub-select?

Help With Query Using Count
I have the following query, but for some reason I can't seem to get the price count part to work.

What I want to do is the get the max and min price for each indivdiual product and I then want to count how many products of the same where found with these different prices.

The only problem is that the count part seems to be giving me the wrong totals.

For some it is giving me 783 when there is only 2 of the same products.

Here is the query that I have.

PHP

select ides,merchantes.merid,merchant,price,min(price) as minprice,max(price) as maxprice,count(price) as pricecount,rand3,title,imageurl,brand,description,pm from merchants,prodsearches where prodsearches.merid=merchants.merid and app=Ƈ' and match (brand,category) against ('"games"' IN BOOLEAN MODE) group by brand,category order by random2 asc

Query Count
I've seen on some sites that there is a way to total up the number of queries ran on a page, and the time it took to run them - as per the bottom of this page:
Is this something which can only be done using PHP, or could I achieve the same result using ASP, by accessing something within MySQL?The reason I ask is that I have a site running MySQL on a Windows server, running IIS and ASP, and would like to set something up like the above.

COUNT() 2 Tables In 1 Query
So I have these two queries:

$result= mysql_query("SELECT YEAR(add_date), MONTH(add_date), COUNT(*) FROM clogsyn_track_main where user_id='$uid' GROUP BY YEAR(add_date),MONTH(add_date)");

$result2= mysql_query("SELECT YEAR(add_date), MONTH(add_date), COUNT(*) FROM clogsyn_track_pop where user_id='$uid' GROUP BY YEAR(add_date),MONTH(add_date)");

They output data like
Month Year, Total Views
So I can see how many total times a page was visited that month for a specific user_id.

The problem is I'm tracking 2 sections of the site "main" and "pop" which pop is a unique page that pops up when something is clicked.

I tried EVERY way to join them together but the count(user_id) doing a LEFT JOIN kept adding the count()'s from both tables together.

Is it not possible to do a count() on two tables in 1 query?

Here's one of the many combined I tried.

$sql = "SELECT a.user_id,b.user_id,YEAR(a.add_date),MONTH(a.add_date),COUNT(a.add_date),COUNT (b.add_date) FROM clogsyn_track_main a LEFT JOIN clogsyn_track_pop b ON a.user_id=b.user_id AND YEAR(a.add_date)=YEAR(b.add_date) AND MONTH(a.add_date)=MONTH(b.add_date) WHERE a.user_id='$uid' GROUP BY YEAR(a.add_date),MONTH(a.add_date)";

Can anyone help me here or is it not possible to do it in 1 query?

Query To Return Two Count()s
I'm trying to figure out a better way of doing this:
SELECT
(SELECT COUNT(*)
FROM GuestEvents
WHERE EventID = 1
AND EventStatus = 'y'
AND UserID = 'bob')
AS Event1,
(SELECT COUNT(*)
FROM GuestEvents
WHERE EventID = 2
AND EventStatus = 'y'
AND UserID = 'bob')
AS Event2
As you can see it's quite repetitive, I wondered if there isn't something like this which could optimise it a bit:

SELECT COUNT(EventID=1) AS Event1,
COUNT(EventID=2) AS Event2
FROM GuestEvents
WHERE EventStatus = 'y'
AND UserID = 'bob'

Count Query Problem
I want to count users by their status, following is the code which I am using currently, instead of using four queries I want to convert this code in one query....

Count Records Within Query
I have the following information in table 'Test1':

field1  field 2
000001  000000
000002  000001
000003  000000
000004  000000

I want to write a query that will only display the unique field 2 records and as well provide a count of how many records have that id.

output should look like

field2  recno
000000    3
000001    1

can some one help with the trick to make this work?

Count Different Distincts In One Query
Any way to count different distincts in one query?

Sub-queries not supported in my MySQL v4.0.??

I have this:

Simple Count Query
I am trying to use the below query to count the number of images held for each book. Unfortunately it is only returning the books with images. I would like all books returned even if they have no corresponding image.

SELECT tblBooks.Title, tblBooks.Description, tblBooks.Author, tblBooks.Annotation, tblBooks.Collection, tblBooks.Price, tblBooks.SortID, tblBooks.Heading, Count( tblImage.ImageID ) AS CountImage
FROM tblBooks
INNER JOIN tblImage ON tblImage.BookID = tblBooks.BookID
GROUP BY tblBooks.BookID

Make A COUNT Query
I want to run a query on quite a big table, about 3,000,000 records.

The query is quite simple
SELECT COUNT(*) FROM TblName WHERE Something = 1
I am only interested to know whether there are at least 10,000 records that meet this condition, so counting the whole table and then comparing the result to 10,000 is overhead. Is there a way to write a query that would simply tell me if there are at least 10,000 (or any other number) records, and would stop executing/counting once it gets to 10,000?

Query Count Return Zero
I'm trying to query a database searching for everytime an event happened in the past 24 hours. I'm using a count(eventid) to add up each event during an hour time.

However, if an event didn't happen say for 2AM then 2AM isn't shown in the return.

How can I get 2AM to show as a row, but with a count of 0 (zero) ??

Here is my qeury...

select count(eventid), DATE_FORMAT(CONCAT(eventdate,' ',eventtime),'%Y%m%d%H') as hour, time_format(eventtime, '%H:00'), eventdate from event where sensorid = '2' and eventdate between '2005-01-19' and '2005-01-20' and DATE_FORMAT(CONCAT(eventdate,' ',eventtime),'%Y%m%d%H%i%s') >= '20050119150018' group by hour order by hour desc;

Help With Query: Order By Association Count
Allright, here are my tables:
Posts:
id | title | text
Comments
id | post_id | name | comment
Basically one post may have many comments. Now, how would I select the 10 posts with most comments, and if only 5 posts contained comments (but there are in fact 10 posts) how could I make this query still return a total of 10 posts?

Select Query With Character Count
can you help me with creating a select query where the character count in a specific column is more than, say, 20 characters?

SQL Query To Include Count From Associated Table
I am having a tough time coming up with the sql for the statement but the long and short of it is this:

Two Tables:

Table 1 is a group - Table 2 is a list of items that belong to a group from table 1.

I want to query all (*) information from table 1 AND get the count of the number of associated items from that group from table 2 in that same query.

Retrieve Everything AND Count Rows In One Query
set rsminmax = con.execute("SELECT * FROM `minirules_minmax` where RuleId = '" & contractId & "'")
set rsminmax2 = con.execute("SELECT COUNT(*) FROM `minirules_minmax` where RuleId = '" & contractId & "'")

Is there any way to do this in one SQL query?

First Group By Date, Then Count... All In One Query?
I have a mySQL database that has individual records for each time a page on my site is hit. What I'm trying to do is set up a tracking system which will display the hit counts for each page, by month, for the past three months, and ordered by total hits over that timespan.

The table is simple:
visitedPage | visitedTime

My current query is this:

Need Single COUNT Value For Complex Query
I have a moderately complex query and I want to page the results, limiting to 50 on a page. I know that in order to do so, I need to know the total number of records so that I know whether there are more records remaining so I can show a 'NEXT PAGE' link.

I'm having trouble constructing a COUNT query from my original query because the query accesses 4 tables, two of which are many-to-one association tables. Here is the original query, without a LIMIT clause:

Count The Hits For Week And Month In One Query?
I have this query to count the amount of total clicks for a whole week, but i would also like to know the total amount of clicks for the whole month. Is it possible to do both of them with one query? Or do i really need to make 2 seperate querys for that?

SELECT date FROM clicks
WHERE date >= '$timestamps[0]' and date <= '$timestamps[6]'
AND link_id = '{$row['ad_linkid']}'

Preventing A Count Query Inside A Loop How?
I have an idea in my head and im pretty sure its possible but I don't have enough mysql knowledge to apply. Basically, I have this:

SELECT user_id FROM ml_group_subscriptions WHERE user_group = $id
then in php I initialise a loop go over the results:
PHP

while ($row = mysql_fetch_array($get_users))
                {
                    $this_user = $row['user_id'];

and then an insert query:

INSERT INTO ml_subscriptions (`user_id`, `campaign_id`) VALUES ($this_user, $add_to_campaign)
while this is all dandy, I don't want duplicate data. My only current method would be
PHP

$get_users = mysql_query("SELECT user_id FROM ml_group_subscriptions WHERE user_group = $id");
                while ($row = mysql_fetch_array($get_users))
                {
                    $this_user = $row['user_id'];
$check = mysql_result(mysql_query("SELECT COUNT(user_id) FROM ml_subscriptions WHERE user_id = $this_user"),0);
if ($check == 0) {
                    $insert_users = mysql_query("INSERT INTO ml_subscriptions (`user_id`, `campaign_id`) VALUES ($this_user, $add_to_campaign)");
}
                }

as you can see this is putting a query inside a loop which could be potentially huge. Can I not adapt the first SELECT query to only accept data that is not about to be duplicated?

Sql Query For Count Records Of Current Week
I have a registration form that saves the registration date on a datetime column. I would like make a query to know how many records are in the table that where for the current week. For example, I'm on week 25 and I want to know how many records I have for this week without the need of specifying the from and to dates. Is there a way to do this?

Trying To Count The Number Of Rows In A Result Set After Query
The user fills out this form to sign up to the website, the form checks the database to see if the username has already been taken with the code:

$conn = mysql_connect("localhost:3306", "root", "********")
                                    or die ("Error With Connection");
        echo("connected<br><br>");
        $db_sel = mysql_select_db("game",$conn)
                                    or die ("Error With Database");
        $check = "select * from users where 'username' = '$username'";
        $db_sel = mysql_query($check,$conn)
                or die (mysql_error());

Query The Article Table And Get A Count Of All Related Comments
Could someone please help me with this query. I have two tables -- one for my articles and another for my comments. Comments are stored in the comments table with a corresponding article id.

I want to query the article table and get a count of all related comments. The following query doesn't seem to work for me. Can you please suggest how I fix it?

SELECT a.id AS id, a.title AS title, a.preview AS preview, a.thumbnail AS thumbnail, a.category AS category, a.timestamp AS timestamp, a.game AS game, count( b.id ) AS commentcount
FROM article_table AS a, comment_table AS b
LEFT JOIN comment_table ON a.id = b.article
WHERE a.news = 'Yes'
AND a.type = Ƈ'
AND a.saved != Ƈ'
GROUP BY b.article
ORDER BY a.timestamp DESC
LIMIT 5

Count One Table's Rows From Multi Table Query
here are my tables (condensed)

FEEDS
feed_id
site_id

SITE
site_id
site_name

ARTICLES
article_id
feed_id
link

I want to create a query that returns the total number of articles for every site_id (which is unique in the SITE table). I have this:

PHP

$gsite = mysql_query("SELECT site.site_id, feeds.feed_id, COUNT(articles.article_id) AS acont FROM site,feeds,articles
WHERE feeds.site_id = site.site_id AND articles.feed_id = feeds.feed_id group by site.site_id", $connection)
or die(mysql_error());

The query does not use JOIN, ON and all that good stuff.

I just need the following variables to run through a loop:

site_id
the number of articles rows per site_id

Extending Simple Query With Specific COUNT() From Second Table
Currently i basically do following:

-----------------------------------
SELECT cat_id, cat_name FROM categorytable WHERE blah=something ORDER BY pos ASC

while(FetchRowAssoc())
{
SELECT COUNT(*) as itemcount FROM itemtable WHERE category=cat_id

echo cat_it, cat_name, itemcount, blah... etc.
}
------------------------------------

I fetch some info from the category table and then I want to know how many items I have in the 2nd table which are associated to the categories in question from the first table.

Can this be done with a single query statement, so i get from FetchRowAssoc the cat_id, the cat_name and the count of the items for this category?

"SELECT t1.cat_id, t1.cat_name, COUNT(t2) as itemcount FROM categorytable as t1, itemtable as t2 WHERE t1.blah=something ORDER BY t1.pos ASC " <- like this but working, i really don't get how to properly write the syntax there... also didn't found a useful example with googling.

Sub Query And Count Query
i have a database with the following structure

id | MoveDate | ItemId | SiteID

(a new entry is entered when an item is moved from 1 site to another)

and i am trying to forumlate a query so that i can count how many days each item was at a specific location
so lets assumes i have the following data

1 | 01/01/2007| 1 | 1
2 | 03/01/2007| 1 | 2

how can i run a query that will tell me that between the dates 01/01/2007 and 08/01/2007 item 1 was at site 1 for 2 days and site 2 for 5

Count Query Causing "excessive Processor Usage"
For the second time in the past 6 months my hosts have shut down my site because it was apparently causing a shared mySQL 5 server to max out at 100% usage. I'm not much a programmer, know even less about mySQL and obviously don't have access to the server from the back end but I'd appreciate any advice as to whether it is my query that is causing the problem:

Set rsMail = objConnC.Execute("SELECT * ,(SELECT COUNT(*) FROM comments WHERE comments.submissionID = submissions.submissionID AND comments.commentInclude = 1) as CommentCount FROM submissions WHERE PigeonHole = 'mailbag' AND Status <> 'hold' ORDER BY submissionID DESC LIMIT 20")

Apart from two instances, this query has been working fine on a page that receives between 10,000 and 30,000 visits a day.

The site is running on Windows and coded in ASP but is calling a separate mySQL server.

Count User Comments In Each Category (was "help On A Query")
I would really appreciate if someone could help me out on this one. this are my tables:

comments

Comment_ID
Video_ID
Comment_Text
Comment_UserID
....
video
Video_ID
Category_ID
.....
category
Category_ID
Category_Name

I'm trying to get a user's count of comments for each category, ordered by the number of comments DESC. basically, I want to show the top categories where the user has posted the highest number of comments.

so far this query gets me the category name of where the user has posted:

PHP

$SQLTemp = "SELECT Category_Name
            FROM category
            WHERE Category_ID
            IN (

            SELECT DISTINCT (    Category_ID        )
            FROM video
            WHERE Video_ID
            IN (

            SELECT DISTINCT (    Video_ID        )
            FROM comments
            WHERE Comment_UserID = $id
            )
            )";

THANKS!!

Count Of *unique* Visits By Page For Each Day (was "Need Help With Query")
Using MySQL 4.1

I have a table 'orders' with the following fields:
ipaddress
pagenum
xtime (which is just the yearmonthday)

this is the query:
SELECT distinct pagenum,ipaddress,xtime,count(pagenum) AS pagenumtotal FROM `orders` group by pagenum,xtime ORDER by xtime, pagenum asc

What I am trying to do is return a count of *unique* visits by page for each day. This query returns a count of *all* visits by page for each day, including repeats. So if a visitors hits a certain page on the site 10 times and adds a record 10 times, the count result will include all 10 records in the count. I want this to only count as 1.

#1136 - Column Count Doesn't Match Value Count At Row 1
I'm getting the above error with the following SQL Statement. I cannot seem to find the error in the code. The select statement does pull multiple rows.

I'm using my SQL version: 4.1.19

Any clue on why this isn't working?

INSERT INTO `Grants` ( `Project_Code` , `Grant_Code` , `Fiscal_Year` , `Capital` )
VALUES (
(
SELECT Project_Code, Grant_Code, FY, SUM( Capital )
FROM Grants_Temp
WHERE Project_Code = 'OSUT'
AND FY = '2006'
AND Claim_Month = '072006'
GROUP BY Grant_Code
ORDER BY Grant_Code
)
)

Add COUNT(*) To Already Existing Query (was "How To Do This")
I have this query:
PHP

SELECT entries.*, categories.*, uploads.*, entries.blog_id FROM entriesINNER JOIN categories ON (categories.category_id = entries.category_id) LEFT JOIN uploads ON(uploads.blog_id = entries.blog_id) LEFT JOIN images ON (images.blog_id = entries.blog_id) ORDER BY entries.date_submitted DESC LIMIT 3

Now i want to add this
PHP

SELECT COUNT(*) FROM comments

but i dont know how to add it into the already existing query. The messages table has a column blog_id column in it so i could do a join like thisd LEFT JOIN comments ON
PHP

(comments.blog_id = entries.blog_id)

but i dont know how to get the first bit in there

Column Count Doesn't Match Value Count At Row 1
I am getting the following error when I run my query.
Column count doesn't match value count at row 1

I have looked up this error and have checked and I appear to have the right number and names in my query. In the DB table I have 34 columns, and that is what I have in the query.

The last 4 in the query are for the names of the images being uploaded, but am not sure how this all works, so I don't know if I need them, but have them there until I know for sure. Comment, Purchase and Remarks are not used in this form, but I have added them so everything is being shown in the query. Code:

Count(*) As Count And TotalCount Of Count
data in myTable1

(n) country_id
(1) 3
(2) 1
(41) 1
(5) 2
(6) 3
(7) 4

data in myTable4

(time) param
(10 : 10) c=4
(10 : 12) c=2
(10 : 30) n=41&k=5
(10 : 35) c=1
(10 : 37) n=5
(10 : 50) c=2
(10 : 54) c=2
(10 : 55) n=1&cate=6
(11 : 12) c=2
(11 : 15) n=7
(11 : 20) c=1
I have data in myTables like the above.

I have the following code.

code

(select left(time,2) as hour, count(*) as count
from myTable4
where

left(param,1)='c'
and
substring(param,3,1)=2

group by left(time,2)
)
UNION all
(select left(time,2) as hour, count(*) as count
from myTable4,myTable1

where
left(param,1)='n'
and
substring(substring_index(param, '&', 1) ,3)=myTable1.n
and
myTable1.country_id=2
group by left(time,2)
)

order by hour
And the code above produces the following result.

result

(day) count
(10) 3
(10) 1
(11) 1
The following would-be code doesn't work correctly, but it will show what I want.

would-be code

(select left(time,2) as hour, count(*) as count,
sum(count) as totalCount
from myTable4
where

left(param,1)='c'
and
substring(param,3,1)=2

group by left(time,2)
)
UNION all
(select left(time,2) as hour, count(*) as count,
sum(count) as totalCount
from myTable4,myTable1

where
left(param,1)='n'
and
substring(substring_index(param, '&', 1) ,3)=myTable1.n
and
myTable1.country_id=2
group by left(time,2)
)

order by hour
And the following is my target result.

target result

(day) count totalCount
(10) 3 5
(10) 1 5
(11) 1 5

Mysql COUNT
Hi there i have a question about the ues of COUNT.

I have a table that contains rows of hit information on a web site. Basicly the fields that im intersted in showing are page_id and is_unique.

page_id contains numbers 1,2,3,4 etc and is_unique only has a "yes" or a "no" in the feild.

So some typical rows look like this:

MySQL Can't Count
If I use COUNT, I get huge results, e.g. 72057594037927936 instead of 1, 2 * 72057594037927936 instead of 2 and so on.

There are no errors in my SQL commands, because they work on other systems. I use a XAMPP installation under MacOS X 10.4.11 and MySQL 5.0.51a.

Mysql Count(*)
I am having this interesting SQL problem with mysql Ver 12.22 Distrib
4.0.16, for apple-darwin7.0 (powerpc). For some reason count(*) does
give the right result, when I replace it with sum(1) it works.

mysql> select term, year, count(*) from course where year = 2001 group
by term, year;

+--------+------+--------------------+
| term | year | count(*) |
+--------+------+--------------------+
| Fall | 2001 | 360287970189639680 |
| Spring | 2001 | 360287970189639680 |
+--------+------+--------------------+
2 rows in set (0.00 sec)

mysql> select term, year, sum(1) from course where year = 2001 group
by term, year;
+--------+------+--------+
| term | year | sum(1) |
+--------+------+--------+
| Fall | 2001 | 5 |
| Spring | 2001 | 5 |
+--------+------+--------+
2 rows in set (0.00 sec)

Code:

Mysql Count
i have a table data as below:

Item
pepsi
pepsi
orange
milo
orange
milo
milo
orange
pepsi
pepsi
pepsi

i want to count and get the maximum data, which is pepsi in this case, how should i do in mysql

MySQL Count Headache!
I need some help adding a count field to my query.

At the moment in my content mangement i have the following query working to display my rows.

PHP

SELECT DISTINCT main_nav.navID AS mainNavID, main_nav.name AS Main_Name, sub_menu.subnavID AS subnavID, sub_menu.navID AS Sub_Nav_Ref_ID, sub_menu.name AS Sub_Name
FROM main_nav
LEFT JOIN sub_menu ON sub_menu.navID = main_nav.navID
ORDER BY navorder
LIMIT 0 , 30

This produces the following result :

What i need to do is add the count of the different Sub_Nav_Ref_ID for each row.

Any help would be brilliant, as i cannot get the counts & the above query in one query which is what i need,

Count MySQL Columuns
I have that to count the columns of a table that will be modified by the user.

The user can insert a new column in the table. Below I demonstrate as it is the structure of the table.

Ex.:

TABLE XXX
ID | RegistoID | Item1 | Item2 | Item3

What it is allowed is to create new itens in the table and would like to count them.

Select , Count(*) In MySQL
I write the following code in vb6 its work fine for Access and MSSQL but not work with MySQL, RecordCount return -1 always kindly help me..

Dim mms As ADODB.Connection

Set mms = New ADODB.Connection
mms.ConnectionString = "DRIVER = MYSQL ODBC 3.51 Driver;SERVER=10.48.60.183;DATABASE=mms;UID=kma;pwd=abcd
mms.Open

Set rstIWOrder = New ADODB.Recordset

SelectString = "select * from IWOrder where WON ='"
SelectString = SelectString + txtIWO.Text + "'"
rstIWOrder.Open SelectString, mms, adOpenStatic, adLockReadOnly

If rstIWOrder.RecordCount = 0 Then
MsgBox "Internal Work Order not Found in Database"

else

or

SelectString = "select count(*) from IWOrder where WON ='"
SelectString = SelectString + txtIWO.Text + "'"
rstIWOrder.Open SelectString, mms, adOpenStatic, adLockReadOnly

Count Problem In From ASP For MySQL
I am executing a statement which returns the rows from a table in MySQL from ASP. I am trying to get the number of rows fetched from the database and I get it as -1. Code:

Php, Mysql And Select Count
I use as the base an existing system, called PHPNuke. My programming work before are only on Assembler, C, C++, Perl and other Databases than MySQL. In PHPNuke and/or the MySQL Queries contained in it there are sequences for simple counting rows (without further processing). The following sequence is permanently used (as example):

$result = $db->sql_query("select * from "$prefix."_sometable where someting='$foo'");
$numrows = $db->sql_numrows($result);

I would make it rather in such a way:

$result = $db->sql_query("select count(*) as count1 from "$prefix."_sometable where someting='$foo'");
$numrows = intval($db->sql_fetchrow($result['count1']));

Now my hopefully not too stupid question. Is there a reason, why e.g. in MySQL large genuine result quantities must be formed, only in order number of lines to determine? I worked so far actually only with other databases. But it is quite possible that that makes any sense here - who knows? Knows here someone the mystery solution?

MySQL COUNT & Return
I'm TRYING to pull every member from a database and count how many articles they have written from one SQL Query.

"SELECT `tbl_user`.*, COUNT(`tbl_product`.`pd_author`) AS `num_articles` FROM `tbl_user`, `tbl_product` WHERE `tbl_user`.`user_statues` = 'active' AND `tbl_user`.`user_name` = `tbl_product`.`pd_author` GROUP BY `tbl_user`.`user_name` ORDER BY `tbl_user`.`user_id`"

Tables:
tbl_user & tbl_product

The Problem:
It works fine except for people with no articles but i still would like it to display there details from tbl_user.

Simple MySQL Count For Non Sql Idiot!
Trying to build a category list with the number of products in each category!

current perfectly working code!

PHP

function fetchCategories()
{
    $sql = "SELECT pd_name, cat_id, cat_parent_id, cat_name, cat_image, cat_description
            FROM tbl_category, tbl_product
            ORDER BY cat_id, cat_parent_id ";
    $result = dbQuery($sql);

    $cat = array();
    while ($row = dbFetchAssoc($result)) {
        $cat[] = $row;
    }

    return $cat;
}

and now i need to add the number of products that occur within each category..been trying for last hour or so with no luck...

Database Schema!
tbl_category

PHP

cat_id
cat_parent_id
cat_name
cat_description
cat_image

tbl_product

PHP

pd_id
cat_id
pd_name
pd_description
pd_price
pd_qty
pd_image
pd_thumbnail
pd_date
pd_last_update

MSSQL To Mysql COUNT() Possible Index
Well we are week 3 in our conversion to mysql from mssql. This one we had to rewrite the mssql version to mysql. We are having issues with a few count statments. Code:

Select Count(*) Problem In Mysql Ver. 4.1
Difference mysql 4.0 and 4.1
My code:

strSelKoresOczek = "SELECT COUNT(*) il_Oczek";
+ " FROM dekretacje d";
+ " left join dekret_realiz dr on d.dekr_id=dr.dekr_id";
+ " left join rejestr_glowny rg on d.prej_id=rg.prej_id";
+ " left join jorg_slownik on
d.jorg_id=jorg_slownik.jorg_id";
+ " WHERE d.symb_rej='RKP' and d.p_rodz='Z'";
+ " and d.dekr_uzytk_id="+ALLTRIM(STR(giUzytkID));
+ " and d.data_czas_dekrwyc is null";
+ " and dr.dekr_id is null"

ok_wyk = SQLEXEC(gnConnHandle,strSelKoresOczek,"cur_sel_koresoczek")

In mysql control center select count(*) return value bigint(21)
Visual fox pro 8 In version 4.0.18 cur_sel_koresoczek.il_oczek is type
Numeric Visual fox pro 8 In version 4.1.16 cur_sel_koresoczek.il_oczek is type Char.

(mysql Count(*) And Delete Command Problem)
I am using mysql 4.1.12 and JDK1.4

In my code I am getting the total number of records(using count(*) command) from the table and if the count exceeds some value, I am deleting few records.

For example, I have one employee table. If the record count exceeds 10,00,000 then I will delete the old 1,000 records using LIMIT in mysql.

select count(*) from employee.(I am getting the count using ResultSet.getInt(count(*)))
if(count>1000000)
{
delete from employee limit 1000 (this command is working fine if i execute in mysql prompt)
}

In my scenario the record count is greater than 10,00,000, but the record is not getting deleted from the table.

I am not getting any exception or error.
(I don't have access to add log messages in the java code)

I don't know where the problem is... whether count(*) is not returning properly or delete command is not working properly.

If i execute those commands in mysql prompt, it is working fine.
Is there any way to see the logs in mysql.