Sql error, Incorrect syntax

Sql error, Incorrect syntax

HI,

I am working on SQL 2000

What is the problem in my below query.

error says

Server: Msg 170, Level 15, State 1, Line 12
Line 12: Incorrect syntax near 'id'.



SELECT DISTINCT
                      *
FROM        
      dbo.ProductSubType
            INNER JOIN
                      dbo.ProductSubTypeLink
                  ON dbo.ProductSubType.id = dbo.ProductSubTypeLink.ProductSubTypeId
            INNER JOIN
                      dbo.ProductSubCategoryLink
            INNER JOIN
                      dbo.Product
                  ON dbo.ProductSubCategoryLink.ProductID = dbo.Product.id
                  


Thanks
tia_kamakshiAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
you are missing the join condition for the  dbo.ProductSubCategoryLink  table...


apart from that, note that you should consider using table alias names...
SELECT DISTINCT  *
FROM  dbo.ProductSubType pst
INNER JOIN dbo.ProductSubTypeLink  pstl
   ON pst.id = pstl.ProductSubTypeId 
INNER JOIN dbo.ProductSubCategoryLink pscl
   ON pscl..... = .....
INNER JOIN dbo.Product p
   ON pscl.ProductID = p.id

Open in new window

0
 
Chandan_GowdaCommented:
You have joined  dbo.ProductSubCategoryLink table but u have not mentioned the column names
0
 
Rick_RickardsCommented:
You're SELECT statement asks for everything * but that will error when everything could be from any one of several tables.  Your * needs to be preceeded by a table name from which you want values.  Also you can not use the * more than one time.

Also there is a missing JOIN in reference to the dbo.ProductSubCategoryLink table.... To make the two examples easier to see here is the before and after picture....

Before - Syntax Error

SELECT DISTINCT
                *
FROM        
      dbo.ProductSubType
            INNER JOIN dbo.ProductSubTypeLink ON dbo.ProductSubType.id = dbo.ProductSubTypeLink.ProductSubTypeId
            INNER JOIN dbo.ProductSubCategoryLink
            INNER JOIN dbo.Product ON dbo.ProductSubCategoryLink.ProductID = dbo.Product.id


AFTER -- Adjust the table with the * to match the one you want and verify that the link added "ON dbo.ProductCategoryLink.ProductCategoryLinkID = dbo.ProductSubTypeLink.ProductCategoryLinkID " fits your situation, (adjusting table names and field names to produce the desired JOIN.

SELECT DISTINCT
                dbo.ProductSubType.*
FROM        
      dbo.ProductSubType
            INNER JOIN dbo.ProductSubTypeLink ON dbo.ProductSubType.id = dbo.ProductSubTypeLink.ProductSubTypeId
            INNER JOIN dbo.ProductSubCategoryLink ON dbo.ProductCategoryLink.ProductCategoryLinkID = dbo.ProductSubTypeLink.ProductCategoryLinkID
            INNER JOIN dbo.Product ON dbo.ProductSubCategoryLink.ProductID = dbo.Product.id




SELECT DISTINCT
                dbo.ProductSubType.*
FROM        
      dbo.ProductSubType
            INNER JOIN dbo.ProductSubTypeLink ON dbo.ProductSubType.id = dbo.ProductSubTypeLink.ProductSubTypeId
            INNER JOIN dbo.ProductSubCategoryLink ON dbo.ProductCategoryLink.ProductCategoryLinkID = dbo.ProductSubTypeLink.ProductCategoryLinkID
            INNER JOIN dbo.Product ON dbo.ProductSubCategoryLink.ProductID = dbo.Product.id
0
 
Rick_RickardsCommented:
**** Reposting my last post to eliminate the unintended duplication it had at the bottom of the post...

You're SELECT statement asks for everything * but that will error when everything could be from any one of several tables.  Your * needs to be preceeded by a table name from which you want values.  Also you can not use the * more than one time.

Also there is a missing JOIN in reference to the dbo.ProductSubCategoryLink table.... To make the two examples easier to see here is the before and after picture....

Before - Syntax Error

SELECT DISTINCT
                *
FROM        
      dbo.ProductSubType
            INNER JOIN dbo.ProductSubTypeLink ON dbo.ProductSubType.id = dbo.ProductSubTypeLink.ProductSubTypeId
            INNER JOIN dbo.ProductSubCategoryLink
            INNER JOIN dbo.Product ON dbo.ProductSubCategoryLink.ProductID = dbo.Product.id


AFTER -- Adjust the table with the * to match the one you want and verify that the link added "ON dbo.ProductCategoryLink.ProductCategoryLinkID = dbo.ProductSubTypeLink.ProductCategoryLinkID " fits your situation, (adjusting table names and field names to produce the desired JOIN.

SELECT DISTINCT
                dbo.ProductSubType.*
FROM        
      dbo.ProductSubType
            INNER JOIN dbo.ProductSubTypeLink ON dbo.ProductSubType.id = dbo.ProductSubTypeLink.ProductSubTypeId
            INNER JOIN dbo.ProductSubCategoryLink ON dbo.ProductCategoryLink.ProductCategoryLinkID = dbo.ProductSubTypeLink.ProductCategoryLinkID
            INNER JOIN dbo.Product ON dbo.ProductSubCategoryLink.ProductID = dbo.Product.id
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.