Page 1 of 1

LEFT JOIN returns every entry but just need one of each!

Posted: Sun Apr 27, 2008 2:36 pm
by mikebr
I am trying to make a query from MySQL using PHP.

I have three tables, category, countries and areas, the countries have the column for their names 'c_country' and another as a auto incremented ID as does the category table.

Work entries are made in the 'work' table using the 'category' id from the category menu and the 'country id' from the country menu, the menus mentioed use an option value of the id and use the name as the menu listing, e.g.

Code: Select all

'<option value="' . $c_id . '>' . $c_country . "</option>\r\n";
I show the category and country menus on a search page that list only the countries in the country meny that have work entries for the selected category, so if there countries of: 'England, Spain, France' and only 'England and France' had work entries then when the category id is passed the countries menu would be populated only with the countries where work entries exist for that category. If there where various work entries for a category under England and France then when that category was selected I would only have those countries listed, so.....

I have been trying a LEFT JOIN but the problem is it returns an entry for each work entry, so the work country menu might look like: 'France, France, France, England, England' and not 'France, England'.

Code: Select all

"SELECT countries.c_id, countries.c_country FROM countries LEFT JOIN work ON countries.c_id = workj_c_id WHERE work.w_cat_id = " . $cat_id;
Anyone know of a way to stop the repeats directly within the query?

Thanks in advance

Re: LEFT JOIN returns every entry but just need one of each!

Posted: Sun Apr 27, 2008 3:26 pm
by mikebr
OK, looks like I got it:

DISTINCT

Code: Select all

"SELECT DISTINCT countries.c_id, countries.c_country FROM countries LEFT JOIN work ON countries.c_id = workj_c_id WHERE work.w_cat_id = " . $cat_id;