Problem with building a query!
Posted: Fri Dec 21, 2007 10:50 pm
feyd | Please use
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:
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]
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_ctyCode: 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_ctyWhat 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]