Sql Command - 2 Inner Joins

[George1111]

Not sure how to do this - keep getting a syntax error

THIS WORKS (One Inner Join)

Select [ACCOUNT],  [KEY], [Stock].[DESC], [GRP], [CLASS], [PRICE], [FROM_DATE], [TO_DATE], [TYPE_CD] from [SpecialPrices] INNER JOIN  [Stock]  ON  [SpecialPrices].[KEY] = [Stock].[Key] ORDER BY [ACCOUNT] ASC

I would like to do 2 Inner Joins so that I can have the Account Name alongside the Account Number

So I presume it will look something like ...

Select [ACCOUNT],  [Debtor].[Account_Name],  [KEY], [Stock].[DESC], [GRP], [CLASS], [PRICE], [FROM_DATE], [TO_DATE], [TYPE_CD] from [SpecialPrices] INNER JOIN [Debtor] ON [SpecialPrices].[ACCOUNT] = [Debtor].[DR_KEY]   INNER JOIN  [Stock]  ON  [SpecialPrices].[KEY] = [Stock].[Key] ORDER BY [ACCOUNT] ASC

The added code is BOLD but gives a syntax error

[sotoasty]

What is the syntax error? For example, MySQL returns something like, you have an error in your query NEAR 'FROM Assignments'.

Could you have a duplicate field name in the list of fields your a pulling(IE, could there be a filed named [CLASS]/[PRICE]... in the Debtor table)?

[RajWolf]

Try This ,

Select 
`SpecialPrices`.`ACCOUNT`,  
`Debtor`.`Account_Name`,  
`SpecialPrices`.`KEY`, 
`Stock`.`DESC`, 
`GRP`, 
`CLASS`, 
`PRICE`, 
`FROM_DATE`, 
`TO_DATE`, 
`TYPE_CD` 
from `SpecialPrices` 
INNER JOIN `Debtor` ON 
`SpecialPrices`.`ACCOUNT` = `Debtor`.`DR_KEY`   
INNER JOIN  `Stock`  ON  
`SpecialPrices`.`KEY` = `Stock`.`Key` 
ORDER BY `ACCOUNT` ASC

[Shaikh.Riyaz.a]

I'd have done it in following way:

SELECT Account, D.Account_Name,  SP.Key, St.Desc, Grp, Class, Price, From_Date, To_Date, Type_CD

FROM SpecialPrices SP, Debtor D, Stock SP 
WHERE SP.Account = D.Dr_Key
AND SP.Key = St.Key
ORDER BY Account ASC

The only problem I found in your code : column 'Key' is ambigious. You haven't specified which 'Key' to read as there are two columns with same name in data source.

[George1111]

Thanks so much Shaikh - you have achieved 2 Inner Joins without even specifying ONE !

Your code also becomes much easier to follow - Great !