Problem with building a query!

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
gabbadude
Forum Newbie
Posts: 1
Joined: Fri Dec 21, 2007 10:10 pm

Problem with building a query!

Post by gabbadude »

feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]


Hello all,

This is my first post here and was really hoping to get some help in building a query.  I am not that good at database queries.  Hence, why i am here.

I have 2 tables, one is a [color=blue]city[/color] table and the other is a [color=blue]property[/color] table.

[color=green][b]City table structure and data:[/b][/color]
[url=http://gabbadude.googlepages.com/city_cty.sql.gz]Download here - (10KB)[/url]

[color=green][b]Property table structure and data:[/b][/color]
[url=http://gabbadude.googlepages.com/property_pro.sql.gz]Download here - (654KB)[/url]

[color=green][b]What I am trying to achieve:[/b][/color]
I am trying to query a count on all the properties in each city and display them as follows:
Fields listed as such
[color=blue][b]id_cty[/b][/color][color=red][b],[/b][/color] [color=blue][b]idare_cty[/b][/color][color=red][b],[/b][/color] [color=blue][b]name_cty[/b][/color][color=red][b],[/b][/color] [color=blue][b]count of properties[/b][/color] in respective cities including the cities that have no properties listed in them which should then show a 0.  But then there is another field that I need to filter on which is the idsts_pro field.  This field is a field that determines whether the property is listed 'for buy' or 'for rent' (1 & 2 respectively).

[color=green][b]The query that I currently have is the following:[/b][/color]
[syntax="sql"]Select a.id_cty, a.idare_cty , a.name_cty, Count(b.idcty_pro) as name
from city_cty as a
left join property_pro as b
on a.id_cty = b.idcty_pro
Group by a.id_cty
Order by a.name_cty
But the problem with this query is that it does not take into account the idsts_pro field. it is currently showing all properties in the respective cities regardless of their type. If I include the idsts_pro fields like this:

Code: Select all

Select a.id_cty, a.idare_cty , a.name_cty, Count(b.idcty_pro) as name
from city_cty as a
left join property_pro as b
on a.id_cty = b.idcty_pro
where b.idsts_pro = 1
Group by a.id_cty
Order by a.name_cty
Then it displays the correct counts but eliminates the 0 values for those cities that have no properties listed for them.

What I need:
I need a way to still display the cities that have no properties listed for them but be able to use the idsts_pro as a parameter depending on what the user clicks.

I would REALLY appreciate some help on this one.

Thanks in advance.

GabbaDude


feyd | Please use[/syntax]

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Post by Christopher »

You might want to try something like "HAVING COUNT(city)>0"
(#10850)
Post Reply