Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
dazzjazz
Starting Member
2 Posts |
 Posted - 2002-12-17 : 23:38:05
|
| Hi,As my DB has a many-to-many relationship, I'm having troubleextracting DISTINCT records.The following SQL is retrieving the correct number of records, however, Inow need to add fields from a tablethat is linked via the many to many relationship.The desired result is to get each productID only once.Query1 = "SELECT productID,productName,isNew,description,imagePathThumb,"Query1 = Query1 & " dateCreate,isOnSpecial,isOnSpecialPrice,price"Query1 = Query1 & " FROM tblProducts"Query1 = Query1 & " INNER JOIN tblManufacturers ONtblManufacturers.manufacturerId = tblProducts.manufacturerId"Query1 = Query1 & " WHERE productID IN (SELECT DISTINCT productsId FROMtblProducts_SubCat)"Query1 = Query1 & " AND tblProducts.isNew=1"Query1 = Query1 & " ORDER BY productName"**********************************************************************************Some info on my table structure:tblProducts_SubCat stores a productID, mainCategoryID, subCategoryId fromtblProducts, tblMainCategory and tblSubCategory respectively.That is to say that 1 Product can be assigned multiple Main Categories andSubCategories.I expect I need to another inner join, something like:INNER JOIN tblProducts_SubCat ON tblMainCategory.mainCategoryId =tblProducts_SubCat.mainCatIDbut I keep getting syntax errorsVery Complex for me. I'd appreciate some helpDarren |
|
|
robvolk
Most Valuable Yak
15732 Posts |
|
|
|
|
|
|
|
Complex SQL