[PHP-DB] Need help with SQL Query
Suppose I have a *BOOKS* table like the one given below: ISBN | Book Title | Author Name 1 Jungle bookSam 2 Princess Diary Joe 3 Titanic Sam 4 House Joe 5 Scary Movie Rick 6 Wonders Harry 7 Java Rick I am trying to write a query which will give the *total number of book titles written by each author*. The final result should be like Author NameCount Sam2 joe 2 Rick2 Harry 1 Can someone help me with this ?? I have tried Select Author Name, COUNT(Book_Title) as Count From Books Group By (Author Name) Will this above query work fine and give the results as shown? Thank you in advance Rikin
[PHP-DB] Need help with MySQL query
Hi!
I need help with simple mysql query.
I have table:
program{
program_id,
partner_id
}
partner{
partner_id,
parnter_name
}
how I can get number of distinct partner_id's where program_id in (
bla bla bla)?
Best wishes,
Mikhail U. Petrov.
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Need help with MySQL query
- Original Message -
From: Mikhail U. Petrov [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Friday, March 05, 2004 11:38 AM
Subject: [PHP-DB] Need help with MySQL query
Hi!
I need help with simple mysql query.
I have table:
program{
program_id,
partner_id
}
partner{
partner_id,
parnter_name
}
how I can get number of distinct partner_id's where program_id in (
bla bla bla)?
select distinct a.partner_id, a.partner_name
from partner a, program b
where a.partner_id = b.partner_id
and b.partner_id in (1, 2, 3)
Best wishes,
Mikhail U. Petrov.
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Need help with MySQL query
- Original Message -
From: Mikhail U. Petrov [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Friday, March 05, 2004 11:38 AM
Subject: [PHP-DB] Need help with MySQL query
Hi!
I need help with simple mysql query.
I have table:
program{
program_id,
partner_id
}
partner{
partner_id,
parnter_name
}
how I can get number of distinct partner_id's where program_id in (
bla bla bla)?
select distinct a.partner_id, a.partner_name
from partner a, program b
where a.partner_id = b.partner_id
and b.partner_id in (1, 2, 3)
sorry for the last line...
and b.program_id in (1, 2, 3)
Best wishes,
Mikhail U. Petrov.
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re[2]: [PHP-DB] Need help with MySQL query
Hi!
Sorry for disturbing, but, at first, this solution isn't right and,
second, I have solved this problem yet. count (distinct blablabla)
is turned to be right.
But thanks...
Hello Viorel,
Friday, March 5, 2004, 12:42:51 PM, you wrote:
VD - Original Message -
VD From: Mikhail U. Petrov [EMAIL PROTECTED]
VD To: [EMAIL PROTECTED]
VD Sent: Friday, March 05, 2004 11:38 AM
VD Subject: [PHP-DB] Need help with MySQL query
Hi!
I need help with simple mysql query.
I have table:
program{
program_id,
partner_id
}
partner{
partner_id,
parnter_name
}
how I can get number of distinct partner_id's where program_id in (
bla bla bla)?
VD select distinct a.partner_id, a.partner_name
VD from partner a, program b
VD where a.partner_id = b.partner_id
VD and b.partner_id in (1, 2, 3)
Best wishes,
Mikhail U. Petrov.
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
Best regards,
Mikhailmailto:[EMAIL PROTECTED]
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] need help with a query
RE c1: I do not know how I was going to record the records of one team vs another in a specific series. That is a very good point and I don't have an answer. I realize this is getting more OT, but if anyone has a nice table structure for handling team v. team series stats I'd like to hear it. Thanks, Kirk -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] need help with a query
If (in MySQL) I have to select 'playerID' from the table soccer.players
based on lname='smith', fname='john', and dob='1988-12-02' and THEN update
soccer.teams 'coachID' equal to the result of the select statement, can I do
it all in one query?
Or must I let my PHP script execute a select, grab the result, and then run
the update statement?
The scenario is this: I've just created the player record in soccer.players
(it's an adult league, so a player could also be the coach) and need to grab
that playerID to insert into the coachID section of soccer.teams.
Here's the definitions for my tables if it would help you follow my question
(with thanks to Micah for the structural assistance):
divisions
Table comments : league division names with identifier
Field Type Null Default
divisionID int(11) No
division_name varchar(15) No
players
Table comments : player registration and statistical info
Field Type Null Default
playerID int(11) No
lname varchar(20) No
fname varchar(20) No
teamID int(11) No 0
dob date No -00-00
address varchar(40) No
telephone int(9) No 2147483647
email varchar(40) No
sex enum('m', 'f') No m
yellow int(2) No 0
red int(2) No 0
disc_notes text Yes NULL
goals_scored int(2) No 0
own_goals int(2) No 0
teams
Table comments : Team registration and statistical info
Field Type Null Default
teamID int(11) No
team_name varchar(20) No
pwd varchar(7) No
coachID int(11) No 0
divisionID int(11) No 0
paid enum('y', 'n') No n
pmnt_notes text Yes NULL
win int(2) No 0
loss int(2) No 0
tie int(2) No 0
goals_for int(2) No 0
goals_against int(2) No 0
Thanks for your help!
Kirk
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] need help with a query
I do not think it is going to work, you may want to stick with the PHP
script. MySQL says that is going to enable subqueries from version 4.1 and
the most recent release is 4.0.16 or 4.0.17.
A question and 2 comments:
q: the teams are fixed within league?
c1: it is not clear how you record the goals of a team against of another.
2. you may want to check the sizes of some fields; some are too big, or
too small.
Mihai
On Wed, 31 Dec 2003, Kirk Babb wrote:
If (in MySQL) I have to select 'playerID' from the table soccer.players
based on lname='smith', fname='john', and dob='1988-12-02' and THEN update
soccer.teams 'coachID' equal to the result of the select statement, can I do
it all in one query?
Or must I let my PHP script execute a select, grab the result, and then run
the update statement?
The scenario is this: I've just created the player record in soccer.players
(it's an adult league, so a player could also be the coach) and need to grab
that playerID to insert into the coachID section of soccer.teams.
Here's the definitions for my tables if it would help you follow my question
(with thanks to Micah for the structural assistance):
divisions
Table comments : league division names with identifier
Field Type Null Default
divisionID int(11) No
division_name varchar(15) No
players
Table comments : player registration and statistical info
Field Type Null Default
playerID int(11) No
lname varchar(20) No
fname varchar(20) No
teamID int(11) No 0
dob date No -00-00
address varchar(40) No
telephone int(9) No 2147483647
email varchar(40) No
sex enum('m', 'f') No m
yellow int(2) No 0
red int(2) No 0
disc_notes text Yes NULL
goals_scored int(2) No 0
own_goals int(2) No 0
teams
Table comments : Team registration and statistical info
Field Type Null Default
teamID int(11) No
team_name varchar(20) No
pwd varchar(7) No
coachID int(11) No 0
divisionID int(11) No 0
paid enum('y', 'n') No n
pmnt_notes text Yes NULL
win int(2) No 0
loss int(2) No 0
tie int(2) No 0
goals_for int(2) No 0
goals_against int(2) No 0
Thanks for your help!
Kirk
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] need help with a query
Mihail Bota [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] I do not think it is going to work, you may want to stick with the PHP script. MySQL says that is going to enable subqueries from version 4.1 and the most recent release is 4.0.16 or 4.0.17. A question and 2 comments: q: the teams are fixed within league? c1: it is not clear how you record the goals of a team against of another. 2. you may want to check the sizes of some fields; some are too big, or too small. Mihai Thanks, Mihail. I'm using PHP 4.3.3 and MySQL 4.0.15-standard (stuck with what my ISP is providing). RE q: I don't understand your question. Do you mean, the teams are fixed for each season? The answer is yes. RE c1: I do not know how I was going to record the records of one team vs another in a specific series. That is a very good point and I don't have an answer. RE c2: I'll check the field sizes, thanks. Regards, Kirk -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
