Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.
Moderator: General Moderators
andre_c
Forum Contributor
Posts: 412 Joined: Sun Feb 29, 2004 6:49 pm
Location: Salt Lake City, Utah
Post
by andre_c » Thu Jul 15, 2004 12:35 am
I can't remember how to do this.
Code: Select all
parents table
-------------------------------------------
parent_id name
1 dodge
2 ford
3 mitsubishi
childs table
-------------------------------------------
child_id name parent_id
1 spirit 1
2 stealth 1
3 focus 2
// I would like to get this result with one query
dodge: 2 models
ford: 1 model
mitsubishi: 0 models
I hope that explains it.
I think it's something to do with joining tables and checking for null but can't remember the code exactly.
feyd
Neighborhood Spidermoddy
Posts: 31559 Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA
Post
by feyd » Thu Jul 15, 2004 12:54 am
I don't remember exacts, but:
Code: Select all
SELECT p.`name`, COUNT( c.`parent_id` ) num_models FROM `parents` p, `childs` c WHERE p.`parent_id` = c.`parent_id` GROUP BY c.`parent_id`might need to be a join...
andre_c
Forum Contributor
Posts: 412 Joined: Sun Feb 29, 2004 6:49 pm
Location: Salt Lake City, Utah
Post
by andre_c » Thu Jul 15, 2004 1:02 am
Thanks for your reply.
The problem is that that select statement returns only the rows that show on the childs table.
what I remember is something like this:
Code: Select all
SELECT p.*, COUNT(c.parent_id)
FROM childs c, parents p
WHERE c.parent_id = p.parent_id
OR c.parent_id = NULL
... now that is not working but i remember that the query that I used a long time ago looked kind of like that.
feyd
Neighborhood Spidermoddy
Posts: 31559 Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA
Post
by feyd » Thu Jul 15, 2004 1:09 am
I have a query I wrote out somewhere around here that does it..
Code: Select all
SELECT count( m.`id` ) `count`, m.`name`, m.`id`
FROM `manufacturers` m
LEFT JOIN `phones` p ON m.`id` = p.`manufaturer`
GROUP BY m.`id`I think was it..
andre_c
Forum Contributor
Posts: 412 Joined: Sun Feb 29, 2004 6:49 pm
Location: Salt Lake City, Utah
Post
by andre_c » Thu Jul 15, 2004 1:11 am
I'll try that, thanks.
[edit] Yup, that was it. Thanks a lot. [/edit]