MySQL/PHP Selecting and counting from multiple tables

Question

I'm not even sure this is possible... I'll show you what I'm working with, then what I would like to do:

state:

state_id

town:

town_id
state_id
misc_property

street:

street_id
town_id
state_id

These are set up in a hierarchy.

What I want to select:

I would like to select all towns with a misc_property, while at the same time selected the state it belongs to and counting all of the streets in that town.

This is what I have so far:

$sql="SELECT 
    a.state_id AS state_id, 
    b.town_id AS town_id, 
    COUNT(c.street_id) 

    FROM 
    state a, 
    town b, 
    street c 

    WHERE 
    b.misc_property='$property'";

Nik · Accepted Answer · 2012-04-18 20:09:17Z

2

Use this query:

SELECT
    town.state_id AS state_id,
    town.town_id AS town_id,
    COUNT(street.street_id) AS count
FROM
    state INNER JOIN town ON state.state_id = town.state_id 
    LEFT JOIN street ON town.town_id = street.town_id
GROUP BY
    state_id,
    town_id
HAVING
    town.misc_property = 'stuff';

edited Apr 18, 2012 at 20:09

answered Apr 18, 2012 at 19:42

Nik

7,28313 gold badges53 silver badges82 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

hitesh israni Over a year ago

good solution, bt should'nt JOIN be LEFT join rather than INNER join? because there may exist a town without street entry. (ofcourse its more of business logic). but am just commenting in the current question's context. cheers..!

lewicki Over a year ago

What if I needed to grab something from the state besides the id?

Nik Over a year ago

I added the state to the joins. You can now put columns from the state table in the select section.

lewicki Over a year ago

Thank you. I removed state_id in the GROUP BY. Not sure why that was needed. For some reason in the HAVING clause town.misc_property was not accepted until i put in the SELECT clause town.misc_property AS misc_property. if you have any thoughts, it would be appreciated.

Hajo · Accepted Answer · 2012-04-18 19:42:22Z

0

you can use GROUP BY town_id with HAVING for special WHERE conditions

answered Apr 18, 2012 at 19:42

Hajo

8496 silver badges21 bronze badges

Comments

Ruslan Osipov · Accepted Answer · 2012-04-18 19:44:56Z

0

Use joins:

SELECT * FROM state
LEFT JOIN town ON state.state_id = town.state_id
LEFT JOIN street ON street.town_id = town.town_id
WHERE town.misc_property = '$property'

answered Apr 18, 2012 at 19:44

Ruslan Osipov

5,8734 gold badges31 silver badges44 bronze badges

2 Comments

Nik Over a year ago

You're not counting the streets.

Ruslan Osipov Over a year ago

You are right, I missed that. Yup, your query seems to be the best way to do this.

ZERO · Accepted Answer · 2012-04-18 19:45:06Z

0

you can do like this

$sql="SELECT 
    a.state_id AS state_id, 
    b.town_id AS town_id, 
    Select COUNT(c.street_id) From C Where C.town_id = b.town_id 

    FROM 
    state a, 
    town b, 
    street c 

    WHERE 
    b.misc_property='$property'and b.state_id = a.state_id "

answered Apr 18, 2012 at 19:45

ZERO

1333 bronze badges

2 Comments

Nik Over a year ago

You should join the tables, not subquery them.

ZERO Over a year ago

yeah join is better but just to make it simple on edit his code

Collectives™ on Stack Overflow

MySQL/PHP Selecting and counting from multiple tables

4 Answers 4

4 Comments

Comments

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

Comments

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related