Hi All,

Im trying to write a sql statement for a search feature which contains 2 relational tables.

Im am getting the right data ok, but getting duplicate records of the main table if 2 or more items in the second table matches

How do i only get it to only output the member once if say 2 of his products match the search phase.


strSQL = "SELECT members.*, membersproducts.* FROM members, membersproducts WHERE (membersproducts.Product LIKE '%"& keywords &"%') AND members.SupplierID = membersproducts.SupplierID ORDER BY members.SupplierID DESC LIMIT "& pagenum &","& results &""



so for example member "tim" has two products product1, product2 and the keyword was product my result would be
tim
tim

how do i get on to be displayed once

Thanks

Tim :)



TDA

TDA

__________________
TDA

You could throw a DISTINCT in there - EG:
strSQL = "SELECT DISTINCT(members.name,...

This should also work:

strSQL = "SELECT members.*, membersproducts.* FROM members
LEFT OUTER JOIN membersproducts ON members.SupplierID = membersproducts.SupplierID
WHERE (membersproducts.Product LIKE '%"& keywords&"%') ORDER BY
members.SupplierID DESC LIMIT "& pagenum &","&
results &""

Wind is your friend
Matt