[RESOLVED] Another count and join issue

[Mobie]

Hello
I've been reading up on all the posts here about counting and joining but without success.....I've tried every option out there and somehow.... well unsuccessfull. I hope somebody can help me out here.

I'm trying to get data out of 2 tables:

table 1 (has listed all cities with City as col)
table 2 (has listed other data including City as Col)

I need to get ALL the 'City' out of table 1 with total counts of 'City' behind the name from table 2 even if it has no records in it like so i.e.

City 1 (0) City 2 (0)
City 3 (4) City 5 (10)
City 6 (1) City 7 (0)
City 8 (8) City 9 (3)

I've tried it like this:

        $node = new sqlNode();
	$node->table = "table 2"; // data including a Col named 'City'
	$node->select = "*";
//	$node->orderby = "GROUP BY City";

if( ($result = $mysql->select($node)) === false )
	die($mysql->debugPrint());

$count = 0;

while ($listing = mysql_fetch_assoc($result)){

$node = new sqlNode();
$node->table = "table 1"; // List of all cities used throughout
$node->select = "*";
$node->where = "where City ='".$listing['City']."'";
$node->orderby = "ORDER BY City ASC";

if( ($RScity = $mysql->select($node)) === false )
	die($mysql->debugPrint());

	$total = mysql_num_rows($RScity);
		while( ($ad = mysql_fetch_assoc($RScity)) ){
	echo "<td width='33%'>"; echo $ad['City']; if ($total!==0) {echo "($total)";} else {echo "(0)";} echo"<td>";

			$count++;
			if($count == $max_per_row){
				echo "</tr><tr>";
				$count = 0;
			}
}}

But this is giving me only the cities with a record from table 2 (not all records even if empty) and behind every city: (1) and not grouped.
I've commented out the group by.... if i do not i get a few listings, all with a 1 behind it even if there are multiple records found with the same city name.

if i take comment out the where clause

$node->where = "where City ='".$listing['City']."'";

I get all cities listed the amount there are records in table 1 with the amount behind it, i.e. 18 cities results in a list of 18 x every city with (18) behind each and every one.

Hope this is not too long of an explaination... Thanks in advance

Mobie

[Roger_Ramjet]

You would do that vwith a LEFT JOIN . Read up about it in the manual and pay attention to the bit about the query returning cols with NULL when table 2 has no data.

[Mobie]

Ok, i fixed it.... can't believe it was that simple....oh well maybe it was the good night rest lol. Anyway for all ppl here is the result:

	$node = new sqlNode();
	$node->table = "cities LEFT JOIN tt_45 ON cities.City=tt_45.City";
	$node->select = "cities.City, count(tt_45.City) AS numrows";
	$node->where = "WHERE IDparent = 1";
	$node->orderby = "GROUP BY cities.City";

if( ($RScity = $mysql->select($node)) === false )
	die($mysql->debugPrint());

		while($ad = mysql_fetch_assoc($RScity) ){

			echo "<td width='33%'>"; echo $ad['City']; echo "&nbsp(".$ad['numrows'].")<td>";

			$i++;
			if($i == $max_per_row){
				echo "</tr><tr>";
				$i = 0;
			}
	}

Thanks for the pinter tho RamJet