Re: JOIN - Not working.

Sorry, that was a bit hasty. I was just trying to get the method across quickly. SELECT SUM(IF(Image1Desc!='insert default value of Image1Desc here'),1,0) as phTotal from PropertyDetails; would count the number of entries where Image1Desc!=default in PropertyDetails. This can be used in combination with a LEFT JOIN or a WHERE or a GROUP BY or anything else to perform the operation you're interested in, like: SELECT SUM(IF(Image1Desc!='insert default value of Image1Desc here'),1,0) as phTotal from PropertyDetails WHERE 'fieldname'='condition'; or SELECT SUM(IF(Image1Desc!='insert default value of Image1Desc here'),1,0) as phTotal from PropertyDetails WHERE 'fieldname'='condition'; or SELECT SUM(IF(PropertyDetails.Image1Desc!='insert default value of Image1Desc here'),1,0) as phTotal from Properties LEFT JOIN PropertyDetails ON Properties.PropertyID=propertydetails.PropertyID WHERE Properties.PropertyType='$PropertyType'; Add a GROUP BY if you need one. Cheers, db "Martin E. Koss" wrote: > Ehm...? > > You've lost me. Perhaps a real-world example may have explained it better. > All the repeated 'whatever' kind of makes it a bit unexplained. > > Martin. > > -----Original Message----- > From: Daniel Barton [mailto:dbarton@prbo.org] > Sent: 28 November 2001 4:14 PM > To: Martin E. Koss > Subject: Re: [PHP-DB] JOIN - Not working. > > Try: > > select sum(if(whatever=whatever,1,0)) as whatever from wherever where > whatever=whatever; > > This is a pretty fast command for simple cross-tabs (outperforms left join?) > and > is easier to understand for a quick fix. You can also write a loop that > creates > a query with multiple rows like: > > select sum(if(whatever=whatever,1,0)) as whatever1 > select sum(if(whatever=whatever,1,0)) as whatever2 > select sum(if(whatever=whatever,1,0)) as whatever3 > select sum(if(whatever=whatever,1,0)) as whatever4 from wherever where > whatever=whatever group by whatever; > > yay! > -db > > "Martin E. Koss" wrote: > > > I have read the parts of the manual(s) but am just not grasping > something... > > > > I have 2 tables, one holds the Property ID etc., and the other holds all > the > > Property Details. They are connected by a field called 'PropertyID' which > is > > a unique ID that appears in both records. > > > > I want to count the number of records in PropertyDetails that have an > entry > > in a field called 'Image1Desc', but I need to do this based on a > previously > > chosen field from Property called 'PropertyType'. However, the code below > is > > simply not giving the results I need: > > > > $phselect = "select COUNT(*) AS phTotal from properties LEFT JOIN > > propertydetails ON properties.PropertyID=propertydetails.PropertyID where > > ((propertydetails.Image1Desc='') AND > > (properties.PropertyType='$PropertyType'))"; > > > > $phresult = mysql_query ($phselect,$conID); > > while ($phrow = mysql_fetch_array($phresult)) { > > $phTotal = $phrow["phTotal"]; > > PRINT "<td align=\"right\">$phresult</td>\n"; > > } // end WHILE > > > > Is there an obvious problem here and can anyone help? > > > > Martin E. Koss > > M: 07946-706459 > > E: mek@mek.co.uk > > > > -- > > PHP Database Mailing List (http://www.php.net/) > > To unsubscribe, e-mail: php-db-unsubscribe@lists.php.net > > For additional commands, e-mail: php-db-help@lists.php.net > > To contact the list administrators, e-mail: php-list-admin@lists.php.net > > -- > ---------- > Dan Barton > Terrestrial Program Biologist > Asst. Data Manager > Point Reyes Bird Observatory > http://www.prbo.org > dbarton@prbo.org > dbarton@grayflycatcher.org -- ---------- Dan Barton Terrestrial Program Biologist Asst. Data Manager Point Reyes Bird Observatory http://www.prbo.org dbarton@prbo.org dbarton@grayflycatcher.org