Complicated MySQL Query... can you help?

[duckduckgoose]

| ID| Country | State     |
| 1 | America | New Jersey|
| 2 | America | New York  |
| 3 | America | Seattle   |
| 4 | Italy   | Rome      |
| 5 | Italy   | Venice    |

Another table has, for example, the name of a friend:

| ID| Friend  | State     |
| 1 | Bob     | Rome      |
| 2 | Joe     | New York  |
| 3 | Max     | New York  |
| 4 | Sam     | New York  |
| 5 | Hal     | Venice    |

One type of link that the user follow looks like this:

http://www.abcdefghi.com/index.php?Country=%&State=New%20Jersey

Then, I build a query that finds all the friends in New Jersey. But, as you can see from sample table, there are no friends in New Jersey. So instead, I want to display all the friends in America.

How do I build a query that will first look in "Country Table" and see what country New Jersey is in, and then, write another query based on that information that will find all the friends from that country as they are listed in "Friend Table"?

I try to read joins, but cannot tell if it will work.

Please help me, thank you very much.

[samudasu]

Is there a good reason why you can't have all fields in 1 table like:

| ID | friend | country | state |

and then use a query like (select * from table where country = 'country' && state = 'state')

[h4x0rmx]

I think you should normalize your db first!
You could have a Countries table with:
id_country, country_name
a States table with:
id_state, id_country, state_name
and a Friends table with
id_friend, id_state, friend_name

I don't know, something like it. You might want to do this if you're considering not just a pair of countries! 😉
But anyways, I don't think a query could do what you want by itself, instead you could use something like:

$query = "SELECT Friend
			FROM friends
			WHERE state='New Jersey';
$result = @mysql_query($query);
if(mysql_num_rows($result)>0){
	/* Show the Friends from New Jersey */
}else{
$query = "SELECT Friend
			FROM friends
			INNER JOIN countries ON friends.state = countries.state
			WHERE countries.country='america';
$result = @mysql_query($query);
/* Show the Friends from America */
}

You might need to check the sintaxis :bemused:, but I think can get the idea!
Hope this helps!
Good luck! 😃

[darkerstar]

If you just want a quick solution you can build two queries.

1 is to get country name of the state

$sql = "select country from countries where state='$state'";

2. to get all friends names from in that country

$sql = "select friends.friend, friends.state from friends, countries where countries.country='$country'";