MYSQL :: Counting Records In 2 Tables Using 1 Query

Counting Records
Got a question for you that is really stumping me. I have 3 tables

job_jobs
------------
job_id
job_name
.
.
.

job_cats
-------------
cat_id
cat_name
.
.
.

job_jobsToCats
-------------
jtc_id
job_id
cat_id

***************

ok i have those 3 tables to categorise the jobs on the site and im trying to build a query that will essentially get all categry names and display the number of jobs in that category so when i output it i will get

accounting (12)
admin(2)
etc
etc

Counting Records
Ive tested the following in PHPMYADIM and it returns each record and how many times it occurrs.

CODESELECT gm, COUNT(*) FROM league_ws GROUP BY gm ORDER BY 'COUNT(*)' DESC

Counting Records
I am trying to figure out how to count a subset of the records returned from a query. I have a query that returns records that fit the criteria events_registrants.pgApproval='1' AND events_registrants.approval='1'. Out of those records, I need to COUNT(events_registrants.pgApproval) which works fine and I also need to count a subset of those records which also fit the additional criteria events_registrants.complete=1. The code below doesn't return the correct count for events_registrants.complete=1. It gives me the total count of all instances of events_registrants.complete (which includes those that are equal to 0) so how would I write.

Counting Records
I have three tables I am trying to do a count on to give me a total amount. The problem is that somes of the sales order numbers are unique to the other tables but I want to count the unique records as well as the records that are not in the other tables. Is there an easy way to do this?

Counting The Records
is it possible to do this in one statement:
I wish to order by a certain field and count the records before a specific record, so as to get a sort of ranking.

Counting Records
I have a downloads system on my website, and for each download I make an entry into a log database, which contains the userid of the person who downloaded it, the fileid, and the date it was downloaded (stored in unix timestamp).
Now, I want to make a stats page, that shows how many file are downloaded each day. How do I group this query? I can't just do it normally via the date field because the value in each record will be different even if they are on the same day.
So, basically I want to count all records grouped by each day, although the date field on each record will be different. How do I do this?

Counting Sub-table Records
I need to write a SPROC that will be pulling information from several tables, altering formats, and inserting into new tables. The query is several hundred lines long. Most of the tables have a related index field.

What I need to do is use a few LEFT JOINs during the SELECT for one of the tables. I need to know, for this table, how many records from some of the other tables have matching Id numbers. I am having trouble figuring out the COUNT statement. Code:

Counting Records In Related Table
I'm trying to construct a single query that returns all records from one table plus a count of all corresponding records in another table. I have a table of members (members) and a separate table (traffic) that tracks what each member has downloaded from the website I've created. Here is the current SELECT statement I'm using:

SELECT * , COUNT(*) AS traffic_count
FROM `members` , `traffic`
WHERE members.id = traffic.member_id
GROUP BY members.id
ORDER BY traffic_count

This almost works for me. The problem is that it only returns results for the members who have corresponding records in the 'traffic' table. This is a great start but I'm trying to return all records from the 'members' table including a corresponding 'traffic_count' variable for each member. This means that for each member that has no entries in the 'traffic' table I'd like the 'traffic_count' variable to be 0.

1 Line Query To Delete Specific Records From Multiple Tables
On clients machine, currently to delete on trainee record it runs 10 queries to delete records from 10 tables. At the time of running all queries, server shows (104) Connection reset by peer. An error condition occurred while reading data from the network.

I think it because of running 10 queries at a same time. Is there any possibility that through one line of query we can delete record from 10 tables.

I've tried following query

DELETE FROM table1, table2, table3, table4, table5, table6, table7, table8, table9, table10 WHERE empID = 11;

But it gives ' error in query.

Counting The Number Of Records That's Greater Than The Limit
I have a table that has thousands of schools in it. I'm returning all the schools in a certain state. I'm limiting it to 20 per page. However, I want to display text that says how many there actually are...exampe: 'showing 1-20 of 43'.

Is this possible? My query is below:

SELECT id, city, CONCAT(school_fname, school_lname) AS thename FROM ccn_schools WHERE state='KS' ORDER BY CONCAT(school_fname, school_lname) LIMIT 20

So how do I have to run this query without a limit to get that number?

Counting Tables
i've been through this and a few forums and the docs, and don't seem to have come across a way of counting how many tables are in a database.

specifically while using a like 'sometable%' would be useful for me

is this possible?

if there is not a command for this, i see that

mysql>show tables like 'sometable%';

returns a table count at the bottom, maybe i can retrieve that somehow?

Counting From Different Tables
I have a problem getting counts from 2 different tables, via joins from the parent table.

At the moment, I am doing this:

CODESELECT q.*, q.id as q_id, t.town, c.category,
  count(qa.id) AS answers
FROM qq_local q
  LEFT JOIN qa_local qa
    ON q.id = qa.q_id
      AND qa.active = 1
  LEFT JOIN towns t
    ON t.id = q.town_id
  LEFT JOIN q_category c
    ON q.category_id = c.id
  GROUP BY q.q
  ORDER BY q.q_id DESC

Counting From Multiple Tables
I have a question about my query. Hopefully I place it in the right section.
I want to count some values from several tables.
When I run this query, the count values are wrong. When I only count from table 'bestellingen' (orders) or 'facturen' (bills), the count values match with what they should be.

The query:
"SELECT l.naam AS leverancier, COUNT(b.bestellingen_id) AS 'totaal aantal bestellingen' , COUNT(bo.bestelorders_id) AS 'totaal aantal bestelorders', COUNT(f.facturen_id) AS 'totaal aantal facturen'
FROM leveranciers AS l ...................................

Counting Data Using 2 Tables
I have two tables, one has students information in, the key fields for this problem are StudentID which is the Primary Key and Gender. The second table has details of reports the students have done, the key fields required for this problem are ReportID (PK), StudentID, ReportDay, Report Date. As detailed below.

Table 1 called Students

StudentID (PK)
Gender

Table 2 Called Reports

ReportID (PK)
StudentID
ReportDay
ReportDate

What I need to do is count the number of Boys who have been on report on Monday between two set dates, I can query Table 2 to get the total number of students who have been on report on Monday between $StartDate and $EndDate but I don't know how to extract the data from Table 1 so that only Boys are counted.

The common link is the StudentID, unique in Table 1. Code:

Counting Query
Is there a MySQL function that i can use to just count the number of rows in a table instead of listing all the IDs and running a command on the result?

Counting Query
I need a slect statement to return the count of rof rows if a particular field matches a criteria similar to the COUNTIF function in Excel. We are using mySQL 3.23.58. Is there a way to do this and if so how?

Query For Counting Unique Values
I'm trying to find out if I can construct a query to MySQL that will return the number of unique values in a given column. The reason is because I have a column that just contains just the YEAR of a given report. I want to see how many different values populate the YEAR column through the entire query result so I know how many tables to render on screen (one table for each year).

Maximum Allowed Tables/db, Fields/tables, Records/table
1)tables that can be created in a MySql database.
2)fields that can be created in a MySql table.
3)records allowed in a MySql table and in a MySql database.
4) allowed joins in a MySql table/database.

Help With 3 Table Query & Counting Rows In Third Table
I have three tables, a members, a vendors and a products. I'm using a query to grab all of the rows from the first two tables, matching on a primary key. This is easy and works fine. However, I'm trying to pull the number of products from the third table and it's giving me trouble. Basically, I can count the products for each vendor if products exist, but if there are no products, instead of returning 0, no rows are returning.

current query looks like this:

SELECT t1.*, t2.*, count(*) AS count FROM vendors as t1, members AS t2, products AS t3 WHERE t2.mem_id = t1.mem_id AND t3.vendors_id = t1.vendors_id GROUP BY t1.vendors_id;

So, all of the vendors that have zero products are being left out of the results. Does anyone know if it's possible to achieve this without using a temp table?

Count Records From Two Tables
I have two tables and I want to count the number of records where field1 = 1.

tbl1    field1
           1
           2
           1
           0

tbl2    field1
           0
           0
           1

Result should be 3

Can someone help with the right coding?

Transfering Records Between Tables
I have two tables 'gamestats' and 'gamestats_archive', i want to move x records from gamestats to gamestats_archive and then delete the records that have been moved from gamestats.

Here is the queries that I am using:

first this runs:

INSERT INTO gamestats_archive SELECT * FROM gamestats WHERE date='$selecteddate' ORDER BY entry_id ASC LIMIT $maxstats

then this one:

DELETE FROM gamestats WHERE date='$selecteddate' ORDER BY entry_id ASC LIMIT $maxstats

the problem is that the delete query is not deleting the records that have been moved, its deleting different ones. does anyone know why this might be happening?

Records From Linked Tables
can anyone help me with an sql statement that will work for mysql to take data from two tables where the two tables are linked with a common field.

Deleting Records From Multiple Tables
I'm trying to delete an article and the corresponding comments to this article

articles
+------------+
| article_id |
+------------+
| 13 |
+------------+
| 16 |
+------------+

comments
+------------+--------------------+
| comment_id | comment_article_id |
+------------+--------------------+
| 1 | 13 |
+------------+--------------------+

mysql_query("DELETE articles, comments
FROM articles, comments
WHERE articles.article_id = 16
AND comments.comment_article_id = 16");

Because article 16 has no comments, the article doesn't get deleted. If I try to delete article 13, this will work.

So how can I delete article 16 even if there are not any comments at all?

Join 2 Tables And Limit Records From One
i've got 2 tables:

Gallery
Gallery_Id,Gallery_Name

Images
Images_Id,Gallery_Id,Images_Filename,Images_Width,Images_Height

I'd like to select all galeries from Gallery and join Images to each gallery but i'd like to limit 3 images per gallery this code list all images from all galleries, i suppose that i must try with subqueries, am i wrong ?

SELECT a.Gallery_Id,a.Gallery_Name,b.Images_Id,b.Images_Filename
FROM Gallery AS a INNER JOIN Images AS b ON b.Gallery_Id=a.Gallery_Id

Time Difference Between Records From Two Tables
I have two tables with data from two separate data loggers. The timestamps of the records are not perfectly synchronised (eg. 2005-07-11 22:50:00 and 2005-07-11 22:49:58). All data must be joined to one table and all records that are out of sync by more then 30 seconds must be rejected. I tried somethig like this: Code:

Can't Update Records When Using Linked Tables In Access
I have a linked table to MySQL 5.0 through MS Access 2003 that utilizes a timestamp field in order to prevent the #Deleted signature happening everytime I create a new record. However, when I attempt to update a previous record I receive the error message: "Reserved error (-7776)" and am subsequently prevented from updating any records. Please note, there are no errors when creating new records, only when I attempt to update an existing one. However, if I remove the timestamp field, updates occur without error. Has anyone else encountered this issue? How might I rectify it?

Would MySQL Handle Millions Of Records Tables
I already found on the MySQL web site that some users did have good
results with tables of some millions records.

But, what I want to do is store some hundreds millions records in a
table. 190 millions to start and maybe a lot more after. Does someone
already use MySQL with such a quantity of data ?

I was also asking myself how a simple SELECt query like this one below
would perform on such a table.

SELECT * FROM MyTable WHERE MyPrimaryKeyField = Value;

I think that with indexes this shoudl be fast despite the fact that
the table is huge.

An other kind of query i'd like to do is :

SELECT Count(*) FROM MyTable WHERE ABooleanField = True;

I expect this one to take several minutes, maybe hours. Is my guess
right or could it be lot faster/slower ?

Join 2 Tables And Select Only Most Recent Records
I have been wrestling with a way to select only the most recent records for a group of clients without using a subselect. I have two tables: cdp_clients and cdp_level_records related by 'admitid'. For each cdp_client there are multiple cdp_level_records, each with a different 'level_date'. I want to select only the most recent cdp_level_record for each client in cdp_clients.

Join Tables To Count Number Of Records?
I've two tables:

responses
==========
response_id
schema_id
timestamp

answers
==========
answer_id
response_id
answer

(that's a cut down version, but will do for this question)

I need to get all the responses where there is at least one answer in the answers table. But I do not want the answer data. It's literally a quick check for an export to say "get me all the responses where there's at least one question answered". I have this: Code:

Get List Of Tables Containg Specific Records
I have a database with 20+ tables that contain data from various instruments. Samples can be measured on any combinaiton of instruments and I would like to be able to query the database to find which tables have data for a specific sample, with the table names being returned to build a dynamic web form for selecting and viewing data.

I can build sort of brut force queries via php to do this to mitigate re-writing webpages when additional tables are added, but it seems to me there should be an elegant solution (or at least brut elegant) to this.

Copy Records From Tables To Remote Database
there ara two mysql server server1 and server2. server1 ip adress is 192.168.1.2 and
server2 ip adress is 192.168.1.3. both servers have same database. i want to copy records server1 to server2 have can i do this?

Deleting Records Upon Query... (!?!)
Sorry for the confusing thread title. Here's what I am trying to do...

Read the last six added records for a particular criteria, and delete the rest. Now consider I have around 1000 unique "criteria" and this query is executed a few times a day for each unique... should I delete it on each read or put it in a cron job and schedule it for every night.

Here's a sample MySQL syntax for my selectquery:

SELECT * FROM table1
WHERE field1 = sometext
ORDER BY some_time_field ASC
LIMIT 0,6
So the questions, again, are:Can I join this SELECT query with a DELETE, and delete all field1s with "sometext" which were not selected by the query?If I can't, how would I do it with a seperate DELETE query? i.e. Leave the last six records for field1 = sometext and delete the rest?Do I really need to order by some_time_field? Or does MySQL have an internal schema to sort records by their "internal" creation date?

Query Help Looping Through Records
The below Query loops through two tables in mysql and outputs all records where a match_date in a reports table (re) is equal to a match_date in a fixtures table (f).

Now this works well, but what I want to do is say to my query is find all of these dates BUT

- As soon as the FIRST f.date is found that does not have a re.date matching it output that information and limit it to 1 .....

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?

Query Cannot Retrieves All The Records
I've got this problem, this query is used to create a combo wich display all months where at least a news is been submited (a kind of hostory menÃ¹), the fact is that it can display max the months wich correspond to the 107 record submited.

The query:

select date_format(data,"%b/%Y")as textMonth, month(data)as month,dayofmonth(data)as day,year(data)as year from archivio where category = "Rassegna Stampa" group by month

Just to be shure I've also tried the query above on mysql browser, and the result is the same. Can you tell me if there's a kind of limit?

Query And Non Existent Records
I have a mysql table texts which keeps textlines in different languages.

columns: textId, languageCode, textline

The application adds records for specific languages as:

textId 1
languageCode: EN
textline: "Username"

another record could be:
textId 1
languageCode: DE
textline "Anwendername"

Now at a certain pojnt in the app I have to check whether for a specific textId there are textline filled in for specific languageCode. For example: Is there a textline for records with textIds 120, 124 and 134. And are these records present for EN and FR (French). I can query as:

Select count(*) from texts where textline != '' and textId in (120,124,134);

But what when the records are there in ENglish and German but not in French?

Any ideas how to solve this?

Query To Get Records In One Table But Not In Another One.
I have this query that works but now I want to refine it so that it only selects records in the loaninfo table that do not have a record in the achtransfer table. I not sure how to do that. Here is the query I have now....

Total Like Records Query
Ime probably missing the boat here so please be patient. I am trying to create a query to display the total number of records with the same parent name.
Eg
Total records for author Eddings = 10

So in effect i want to count all the entries and display only the numeric figure of books for that author. I think its supposed to look something like:

Select
sum(Eddings) $total
From books

MySQL Query For All Records With NULL Value
I have recently been trying to extract a few products from a database (that is quite big), so that I can find products that have a specific NULL Value, what kind of a SQL statement would allow me to do this?

Update A Set Of Records In One Single Query
My records contain a position, from 1 to n. This position is not the primary key.
In normal conditions, the position of this records should be as follows:
1, 2, 3, 4, 5, 6, 7, 8, 9 . etc...

One of my utitities is meant to check the consistency of the table -
one of the things it has to do is scan the table as follows:

SELECT position FROM employe_records WHERE employe_id = ? ORDER BY position

From the result of this query I verify each entry (JDBC) one by one, looking for inconsistancies. In a for() loop I look at the value of position and check that they start from 1 and are incremented by one for each record.

At the first inconsistency I come accross I want to run a query to update the records so it is consistant again - it is something like this:

UPDATE employe_records SET position = updatedValue FROM
employe_records WHERE employe_id = ? ORDER BY position

This is not valid query of course - my question is what should my query look like?! How can I make 'updatedValue' incremented by one automatically so each of the records are updated from 1 to n.

How Can I Update A Set Of Records In One Single Query
My records contain a position, from 1 to n. This position is not the primary key.
In normal conditions, the position of this records should be as follows:

1, 2, 3, 4, 5, 6, 7, 8, 9 .etc...

One of my utitities is meant to check the consistency of the table - one of the things it has to do is scan the table as follows:

SELECT position FROM employe_records WHERE employe_id = ? ORDER BY position

From the result of this query I verify each entry (JDBC) one by one, looking for inconsistancies. In a for() loop I look at the value of position and check that they start from 1 and are incremented by one for each record.

At the first inconsistency I come accross I want to run a query to update the records so it is consistant again - it is something like this:

UPDATE employe_records SET position = updatedValue FROM
employe_records WHERE employe_id = ? ORDER BY position

This is not valid query of course - my question is what should my query look like?! How can I make 'updatedValue' incremented by one automatically so each of the records are updated from 1 to n.

Query For All Records With Datetime In 2005?
I have a table with a datetime field. I'd like to query all records for 2005 based on that field. I'm having trouble coming up with a where clause that does this.

I'm on MySQL 4.1

Select Last N Records Matching A Query.
I am trying to select the last n records in a database which conform to criteria selected by a user. I gather that there is no "BOTTOM" equivalent to the "SELECT TOP" command. I've tried "SELECT... ORDER BY... LIMIT X,Y" but MySQL doesn't seem to take a second parameter for the LIMIT statement. Is there any way round this?

List The Number Of Records In Query
I have a very simple query. I want to list a bunch of names & how old they are. (two columns) I want to add a third column that basically counts how many records there are.

Example:

1. Bill Jones - 55
2. Bob Smith - 56
3. Steve Gates - 25

Can I do this in mysql or should I add the code in PHP, and how do I do it?

Query To Display Records Over A Certain Number?
think my brain is now officially fried as i can not come up with this query....

i want to display records from my table where the record count is say over 5.

real simple...two columns a and b. 10 records in the table....

IN Query Showing All Records If Used With Same Field Twice
I have 2 tables programs and programType. programs has 5 records and programType has no records. When I run a query like "SELECT * FROM programs where id in (select programid from programType) and id in (select programid from programType)" I get all records But When I try to run "SELECT * FROM programs where id in (select programid from programType)" Then I get no records.

Flag Records Not To Be Reused After Query
I'm a MySQL novice, and I don't even know if there is a way to do this.

I have a simple table containing just 2 fields and about 15,000 entries...one contains an ID (which is just an incremental number) and the other a phrase.

I will be doing queries selecting 50 random entries from the table. Somehow, once those 50 are selected and returned, I would like to flag them so that they cannot be selected again UNTIL I exhaust the entire list. So, I'd like to get 50 random at a time, until all 15,000 are gone, and then start over.

If that isn't possible, I suppose I can just select the first 50 every time and delete the first 50 when I am done.