Hello to everyone,
I have a database with several representatives in different cities, sometimes there are many representatives in one city.

The search is like:

sql_representatives.source = ("Select ID, CITY from REPRESENTATIVES Where PROVINCE_ID = " & LST_PROVINCE.ItemData(LST_PROVINCE.ListIndex) & ";"

The above sql return all cities in the database. I need to have both ID and CITY, so I can not use

sql_representatives.source = ("Select DISTINCT CITY from REPRESENTATIVES Where PROVINCE_ID = " & LST_PROVINCE.ItemData(LST_PROVINCE.ListIndex) & ";"

as i do not get the ID.

How can I solve this problem?

I thank you for your time.

please post the code as used with the form and connection for a clear perspective and solution

Below code will return the named columns

Select ID, CITY from REPRESENTATIVES Where PROVINCE_ID = " & LST_PROVINCE.ItemData(LST_PROVINCE.ListIndex) & "

I wonder you said that it will just return all the cities..

The above sql return all cities in the database. I need to have both ID and CITY, so I can not use
Help with Code Tags
(Toggle Plain Text)

sql_representatives.source = ("Select DISTINCT CITY from REPRESENTATIVES Where PROVINCE_ID = " & LST_PROVINCE.ItemData(LST_PROVINCE.ListIndex) & ";"

sql_representatives.source = ("Select DISTINCT CITY from REPRESENTATIVES Where PROVINCE_ID = " & LST_PROVINCE.ItemData(LST_PROVINCE.ListIndex) & ";"
as i do not get the ID.

The above query will return two columns...

and to your condition in your where clause I assume that the data comes from the listbox, right?

so let's make it a little simplier...

Select ID, CITY from REPRESENTATIVES Where PROVINCE_ID = " & LST_PROVINCE.Text & "

hope it would help.

regards...

Hi,

Select ID, CITY from REPRESENTATIVES Where PROVINCE_ID = " & LST_PROVINCE.Text & "

the above sql returns all cities in the table. Now I want to display any occurrence once, even if it is several times in the database.

Using DISTINCT returns all occurrences, as the ID is always unique. I tried using GROUP BY CITIES which actually delivers unique occurrences, however, the ID is not delivered :(

Select ID, CITIES From Representatives GROUP BY CITIES

I tries using sub-selects, but for an unknown reason the little brain of the humble me is overstressed with the situation and hangs!

Best regards,
Ali Baradaran

Dear Jireh,
You are totally right. It does not return all cities, but it returns all cities in that specific province. By all cities I mean it also returns duplicate records. I want it to return distinct records from the column cities and the respective ID.

ID | CITY | Province
1 | Hamburg | Hamburg
2 | Munich | Bavaria
3 | Cologne | Nordrhein-Westfalen
4 | Munich | Bavaria
5 | Augsburg | Bavaria
6 | Ulm | Bavaria
1 | Harburg | Hamburg

I want to get the following result:

ID | CITY | Province
1 | Hamburg | Hamburg
2 | Munich | Bavaria
3 | Cologne | Nordrhein-Westfalen
5 | Augsburg | Bavaria
6 | Ulm | Bavaria
7 | Harburg | Hamburg

If I use DISTINCT it returns all values, as the ID Column is unique. I tried GROUP BY as follows:

Select Min(ID), CITY From Representatives Where PROVINCE = 'BAVARIA' Group By CITY.

It correctly returns the cities once, but it does not return the ID, at least I can not retrieve the ID.

Best regards,
Ali Baradaran

Dear All,
I appreciate your kindness very much. The solution of this problem was in the following SQL Statement:

Select Min(ID) AS ID, CITY From Representatives Where PROVINCE = 'BAVARIA' Group By CITY.

This SQL did not work as I did not mention

Select Min(ID) As ID

which lead to an error.

I hope this is a solution for anyone who has the same trouble.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.