0

I need to select all unique UK cities, where customers and suppliers live, sort from A to Z

I have two tables: Customers and Suppliers. They both have City and Country columns.

This is my request:

select distinct Customers.City, Suppliers.City, Customers.Country, Suppliers.Country
from Customers
join Suppliers
on Customers.Country = Suppliers.Country
where Customers.Country like "UK";

And as a result I get 4 columns: City, City, Country, Country.

Where am I wrong?

David
  • 4,665
  • 4
  • 34
  • 60
Amayak
  • 39
  • 7
  • You're getting 4 columns because you're asking for 4 columns. I'm unclear on what you want. "Show me the cities/countries of suppliers that also have a customer within that same city and country?" – Kritner Jan 25 '17 at 17:39
  • You don't want a JOIN you want a UNION – Uueerdo Jan 25 '17 at 17:39
  • I need unique UK cities from two different tables. – Amayak Jan 25 '17 at 17:51

2 Answers2

0

You can use least and greatest clause for that. Below answer may help you. MYSQL select DISTINCT values in two columns

Community
  • 1
  • 1
Ravi Koradia
  • 879
  • 7
  • 13
0

Thank you all. Here is right request that works:

select City,Country
from Customers
where Country like "UK"
union
select City,Country
from Suppliers
where Country like "UK"
order by City asc;
Amayak
  • 39
  • 7
  • you don't need LIKE but =. A good SQL primer would probably help. See http://stackoverflow.com/questions/543580/equals-vs-like – mauro Jan 26 '17 at 00:11