newbie join question

[allyeric]

Hi all

I am trying to retrieve information from various tables. That was the easy part. But, one of the fields I would like to return distinct, as the same data can be repeated more than once. Here is my code, this part of it works:

select cat.catregid, s.companyname, cat.supplierID, p.productID, c.catname, c.catcode,
p.name, cat.division from suppliermain s
join catregistration cat on s.supplierID = cat.supplierID
join product p on p.catregID = cat.catregID
join categories c on p.category = c.catcode
where contains (p.description, 'nails') OR p.name = 'nails'
order by s.companyname


I would like it to return the companyname only once. Any assistance in this is greatly appreciated!

Thanks
allyeric
[sig][/sig]

 

[foxdev]

Not sure I follow you, allyeric. If the goal is to perform a join with the various tables, and for one company there are multiple products, etc., and each product, cat, and so forth has its own row, what would you want it to show in the CompanyName column? [sig]<p>Robert Bradley<br><a href=mailto: > </a><br><a href=

http://www.foxdev.com/>FoxDev

- Visual FoxPro Development</a><br> [/sig]

 

[allyeric]

ok .. for each product, there could be a division name and a company name, or just a company name. One company can have more than one division. I want to let the user do a search on the product name, and have it list the companies or divisions who carry this product. In the same table, I will also need to write the category (catname) and product name, but must also carry the values of catcode and catregid (every product has one catregid per division/company). Perhaps if I break down a little more what my tables are like, it might help.

Product table: has prodID (PK), name, category, catregid, description

catregistration table: catregid(PK), division, supplierID, category

category table: catcode(which is same # as category in other tables), catcode

suppliermain table: supplierID(PK), companyname

when the user searches a keyword, have to search description and name in products

from there, I need to print a table showing company or division name, product name, category, and catregid

hope this clears it up a little ??

thanks
ally [sig][/sig]