[SOLVED] mySQL 4 query unique results with a join?

[amites]

hello,

 

I am having trouble getting unique results returned on a query

 

code looks like:

 

		$query = "SELECT distinct c.id, c.english"
			. "\n FROM bil_cities AS c"
//				. "\n JOIN bil_msg_sent AS m ON (m.locid = l.id)"
//				. "\n JOIN bil_location AS l ON (c.id = l.city_id)"
			. "\n JOIN bil_location AS l"
			. "\n WHERE c.id = $val"
//				. "\n AND c.id = l.city_id"
//				. "\n GROUP BY c.english"
			. "\n ORDER BY c.english";
	$result = mysql_query($query);

 

with the different attempts I've made so far  I get one of 2 results,

 

1: no results

2: 9 references to the same entry

 

any ideas?

[amites]

one of my bang head here moments,

 

was comparing the value of the state ID to the city ID

[amites]

for future reference the code ended up looking like:

 

		$query = "SELECT c.id, c.english"
			. "\n FROM bil_cities AS c"
			. "\n JOIN bil_location AS l"
			. "\n WHERE c.state_id = $val"
			. "\n AND c.id = l.city_id"
			. "\n ORDER BY c.english";
	$result = mysql_query($query);

 

solved this one when going to clarify my question,

 

in this case $val is the ID from a table of US states