[SOLVED] Checking a table then limiting another

[laPistola]

Right ill try explain the best i can.

 

I have two tables in MySQL units and buildings, buildings list one of each building that owned, it included the address name lat and lng, units list all the units owned inside each building, in the units table there is a column bid which contains the building ID number to which building in the buildings table its in.

 

When a user does a search for buildings which have available units in closest to the address they input it starts a MySQL query.

 

I want to only return buildings that have available units in order of distance i also need to list all available units inside that building but in order by size so i do this else where. this is my MySQL command

 

SELECT units.bid, units.status, buildings.*,
(((acos(sin((".$latitude."*pi()/180)) * sin((buildings.`lat`*pi()/180))+cos((".$latitude."*pi()/180)) * cos((buildings.`lat`*pi()/180)) * cos(((".$longitude."-buildings.`lng`)*pi()/180))))*180/pi())*60*1.1515) as distance FROM units, buildings WHERE units.bid = buildings.bid AND status = 0 ORDER BY distance ASC LIMIT 0, 5

 

and the related code in the loop:

 

<?php do { ?>
            // TABLE AND INFO ON THE CURRENT BUILDING 
              $bid = $row_buildingsRS['bid'];
              echo getUnits($database_utl, $utl, $bid); // FUNCTION THAT CALLS THE UNITS FROM A DIFFERENT QUERY 
             } while ($row_buildingsRS = mysql_fetch_assoc($buildingsRS)); ?>

 

Obvoulsly whats happening is when the first query is made it retuning one row for each available unit, as there is say 5 units available in say building 1 its then looping through and displaying the same block with units and info on building 1

 

To show you more what i mean i taken a screen shot and attached to this post

 

How can i still do the check if the building has any available units but then only return one row of each building with availabe units??

 

Thank you for any help!

 

 

 

[attachment deleted by admin]

[J.Daniels]

Try using GROUP BY:

 

SELECT units.bid, units.status, buildings.*,
   (((acos(sin((".$latitude."*pi()/180)) * sin((buildings.`lat`*pi()/180))+cos((".$latitude."*pi()/180)) * cos((buildings.`lat`*pi()/180)) * cos(((".$longitude."-buildings.`lng`)*pi()/180))))*180/pi())*60*1.1515) as distance FROM units, buildings WHERE units.bid = buildings.bid AND status = 0 GROUP BY units.bid ORDER BY distance ASC LIMIT 0, 5

[laPistola]

Bang on JD thank you

 

I had thought about using a group by but assumed it would gather the same results, look where assuming got me :S

 

Thanks again