Page 1 of 1

select from two tables...

Posted: Thu Jul 15, 2004 12:35 am
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.

Posted: Thu Jul 15, 2004 12:54 am
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...

Posted: Thu Jul 15, 2004 1:02 am
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.

Posted: Thu Jul 15, 2004 1:09 am
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..

Posted: Thu Jul 15, 2004 1:11 am
by andre_c
I'll try that, thanks.

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