Your query won't work because a subquery used as an expression can only return one row with one value. You'll have to include a WHERE clause to constrain the subquery to the group in question. You also have an issue because the query is not correctly formed.
Select
a.*,
(Select
b.BasePrice * c.GroupFactor
FROM
TableA a2
INNER JOIN TableC c ON a2.Group = c.Group
where c.groupid = a.groupid) as x
from
TableA a ,
INNER JOIN TableB b ON a.OrderedItem=b.OrderedItem
Now I am curious why Sergey's response won't work (aside from the incorrect syntax ;-) )
Select a.*, b.BasePrice * c.GroupFactor AS Amount
from TableA a,
JOIN TableB b ON a.OrderedItem=b.OrderedItem
JOIN TableC c ON c.GroupID = a.Group
I would expect both of these queries to generate the same results.
-Mike
>Hi this is what I would like to accomplish.
>
>TableA
>-------
>OrderedItemID
>OrderedItem
>Group
>
>TableB
>--------
>OrderItemID
>OrderItem
>Baseprice
>
>TableC
>--------
>GroupID
>Group
>GroupFactor
>
>
>
>I receive an error when I try this statement.
>
>Thanks for the help,
>
>Alvin