multiple tables, 1 query

epice

I have 2 tables, profiles and business. Each of the tables contains a state field and a city field. I need to query both tables based on the state and to group by the city. So far, this is what I've come up with:

$q="select business.city, profiles.city from business, profiles where (business.state='$state') and (profiles.state='$state') group by business.city, profiles.city";

Then I try to spit out the results with:

while ($row=mysql_fetch_array ($r)) {while ($row=mysql_fetch_array ($r)) {
$q="select from business where (state='$state') && (city='$row[city]')";
$q2="select from profiles where (state='$state') && (city='$row[city]')";
$t=mysql_query($q, $l);
$t2=mysql_query($q2, $l);
$n=mysql_num_rows ($t);
$n2=mysql_num_rows ($t2);
$num=$n+$n2;
print ("<tr><td><a href=\"directory.php?catstate=$state&city=$row[city]\">$row[city]</a> ($num)</td></tr>\n");
}

What end up happening is I only get the cities from the profiles table and nothing from the business. I know that I probably need to use some kind of join, but I am clueless as I have only been programming for a short time. Any help in solving this problem will be greatly appreciated.

Cheers!

altexis

There is an SQL command that does the job for you.
Its the UNION command... but I doubt MySQL supports it. Maybe someone else can correct me on this.

Your query cannot work properly because you have not stated any connection between the "profiles" and "business" tables. Usually one table has an ID number (say table business) and another table has a connection to this ID number (say table profiles has a businessID field). You need this connection to get your query working.

If these tables are totaly independent then you're looking for the UNION command I told you about. If UNION is not supported by MySQL you should try to use arrays to connect the two tables in code. It that case you would need two different queries.