Show distinct records from multiplex columns

[Baradaran]

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.

[dspnhn]

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

[jireh]

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...

[Baradaran]

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

[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

[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.