Complex SQL - SQL Server Forums

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.

All Forums

Old Forums

CLOSED - General SQL Server

Complex SQL

Author

Topic

dazzjazz
Starting Member

2 Posts

Posted - 2002-12-16 : 20:52:40

Hi,

As my DB has a many-to-many relationship, I'm having trouble
extracting DISTINCT records.
The following SQL is retrieving the correct number of records, however, I
now need to add fields from a table
that is linked via the many to many relationship.

Query1 = "SELECT productID,productName,isNew,description,imagePathThumb,"
Query1 = Query1 & " dateCreate,isOnSpecial,isOnSpecialPrice,price"
Query1 = Query1 & " FROM tblProducts"
Query1 = Query1 & " INNER JOIN tblManufacturers ON
tblManufacturers.manufacturerId = tblProducts.manufacturerId"
Query1 = Query1 & " WHERE productID IN (SELECT DISTINCT productsId FROM
tblProducts_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 from
tblProducts, tblMainCategory and tblSubCategory respectively.
That is to say that 1 Product can be assigned multiple Main Categories and
SubCategories.

I expect I need to another inner join, something like:

INNER JOIN tblProducts_SubCat ON tblMainCategory.mainCategoryId =
tblProducts_SubCat.mainCatID

but I keep getting syntax errors

Very Complex for me. I'd appreciate some help

Darren

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-12-18 : 17:22:21

hi dazz

maybe you could post some example data
eg
productID, productName
1, twinkie
2, bla

for each table - it would mean you get an answer much quicker...dummies like me can't quite tell where your problem is, until we can re-create it...

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

Edited by - rrb on 12/18/2002 17:23:10

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources