[SOLVED] select from two tables...

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
andre_c
Forum Contributor
Posts: 412
Joined: Sun Feb 29, 2004 6:49 pm
Location: Salt Lake City, Utah

select from two tables...

Post by andre_c »

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.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

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...
User avatar
andre_c
Forum Contributor
Posts: 412
Joined: Sun Feb 29, 2004 6:49 pm
Location: Salt Lake City, Utah

Post by andre_c »

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.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

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..
User avatar
andre_c
Forum Contributor
Posts: 412
Joined: Sun Feb 29, 2004 6:49 pm
Location: Salt Lake City, Utah

Post by andre_c »

I'll try that, thanks.

[edit] Yup, that was it. Thanks a lot. [/edit]
Post Reply