sql inner join / left join question

[jlego]

this is not a web design question, i'm actually using it in vb.net
but they don't have a database forum in the software development section.

what i am doing:
i have two tables. customer and customer_phones

my visual basic program has a search for customer option,
which returns the customers name & telephone number into a listbox.

select customer.name, if(isnull(customer_phones.number, '', customer_phone_numbers.number) as Phone from customer left join customer_phone_numbers on customer_phones_numbers.Customer_ID = customer.ID WHERE customer.Name LIKE '%whatever%' OR customer_phone_numbers.Number LIKE '%whatever%'

inow, the customer phone numbers table can have as many phone numbers as you want attached to the customer by the customer id.
so my code fills up the list box with the same customer for each phone number he has (hopefully you understand what i mean).
when i only want 1 instance of the customer to appear no matter how many phone numbers he has.

i have tried

group by customer_phones.Number

but if there is a relative ect. that has there own account with same phone number, one of them doesnt appear.

select distinct customer.ID, select distinct customer_phones.Customer_ID

doesn't seem to work either

anyone have any suggestions / input?

thanks

[me655321]

I think GROUP BY customer.Name should do what you want.

[baki100]

maybe you can split the query into two where you select the customers name and the select all the customers telephone number and put them into two separate datasets you can then manipulate the datasets however you want, if you want to return one only put a date_created field in the customer_phone_numbers table and select the latest telephone number that was inserted.

[jlego]

me655321 - the problem with grouping by customer name is i have 87k+ customers in the database, and you search for example dan smith, if we have two you are only going to see one.

bak- i used to do two seperate calls. first i would pull the customer name and id, then i would pull a phone number using the customer id and add it to the listview. i was just trying to find a more efficient way of doing this & to just use one call.

thanks for your responses and ideas i appreciate it.

[me655321]

me655321 - the problem with grouping by customer name is i have 87k+ customers in the database, and you search for example dan smith, if we have two you are only going to see one.

Ok so can you group by customer.ID?

[jlego]

wow. i tried everything from distinct customet.ID to grouping by phone numbers.

never thought to use group by customer.id

worked, thank you.