Querying Three Tables

But treating them as one...

[inveni0]

I need to query three tables. Each contains a city and a state. How can I change the GROUP BY clause so that only one instance of each city will appear within the repeated region?

[henry0]

SELECT DISTINCT city, state FROM aaaa WHERE etc...

that should do it
but you will need adapting the query to your join
(if join there is)

Review SELECT/DISTINCT [dev.mysql.com]

[inveni0]

That doesn't group them.

Basically, I have three tables.
Each has a City and State column.
I'll have a URL variable to determine the State.
I need the query to get all rows with that state from each table.
Then, it needs to group the results by city, so that I only have one instance of the city (for all three tables) when the results appear on the page.

I've been trying all sorts of join and left join and right join, and I'm going crazy.

Any help would be great!

[inveni0]

It looks like the UNION statement gives me what I need. However, if anyone has any better ideas, I'm happy to hear!

[omoutop]

you can try something like this...

$my_state = $_GET['State'];
$city_array = "";

$query1="SELECT City FROM table1 WHERE State='$my_state' GROUP BY City ";
$result1 = mysql_query($query1) or die(mysql_error().'<p>'.$query1.'</p>');
while ($myrow1 = mysql_fetch_array($result1))
{
$city_array[] = $myrow1['City'];
}

$query2="SELECT City FROM table2 WHERE State='$my_state' GROUP BY City ";
$result2 = mysql_query($query2) or die(mysql_error().'<p>'.$query2.'</p>');
while ($myrow2 = mysql_fetch_array($result2))
{
$city_array[] = $myrow2['City'];
}

$query3="SELECT City FROM table3 WHERE State='$my_state' GROUP BY City ";
$result3 = mysql_query($query3) or die(mysql_error().'<p>'.$query3.'</p>');
while ($myrow3 = mysql_fetch_array($result3))
{
$city_array[] = $myrow3['City'];
}

new_city_array = array_unique($city_array);

that way you get one instance of all the cities affected by the State restriction
don't know how to do it in one sql statememnt :(

[inveni0]

That's essentially the same as the UNION statement. The union statement just does it a bit quicker with less code.