You can JOIN onto a sub query rather than having it in the select. I came up with this but I can't test it
SELECT FContainerHylla.Name, FContainerPlats.HyllId,
FContainerPlats.x, FContainerPlats.y,
FContainerPlats.enable, FContainerHylla.Type,
ISNULL((SELECT MIN(fcontainer.id) AS id
FROM FContainer
WHERE (PlatsId = FContainerPlats.id)), 0) AS FcId
,SubQ.Produktnamn
FROM FContainerPlats
INNER JOIN FContainerHylla ON FContainerPlats.HyllId = FContainerHylla.Id
INNER JOIN (SELECT FContainer.id AS SubID, produkter.Produktnamn AS Produktnamn
from FContainer INNER JOIN
Stämplingar ON FContainer.id = Stämplingar.ID INNER JOIN
Tempo ON Stämplingar.temponr = Tempo.temponr INNER JOIN
Produkter ON Tempo.produktnr = Produkter.Produktnr) SubQ ON SubQ.SubID = FContainerPlats.id
WHERE (FContainerHylla.Type IN (1, 6, 10))
ORDER BY FContainerPlats.HyllId, FContainerPlats.x, FContainerPlats.y
I might have the key on the inner join to "SubQ" wrong.
Alternatively you could set them up as two Common Table Expressions and join them that way (again this is untested and watch out for the id I've used in the join)
;WITH CTE1 AS
(
SELECT FContainerHylla.Name, FContainerPlats.HyllId,
FContainerPlats.x, FContainerPlats.y,
FContainerPlats.enable, FContainerHylla.Type,
ISNULL((SELECT MIN(fcontainer.id) AS id
FROM FContainer
WHERE (PlatsId = FContainerPlats.id)), 0) AS FcId
FROM FContainerPlats
INNER JOIN FContainerHylla ON FContainerPlats.HyllId = FContainerHylla.Id
WHERE (FContainerHylla.Type IN (1, 6, 10))
),
CTE2 AS
(
SELECT FContainer.id AS SubID, produkter.Produktnamn AS Produktnamn
from FContainer
INNER JOIN Stämplingar ON FContainer.id = Stämplingar.ID
INNER JOIN Tempo ON Stämplingar.temponr = Tempo.temponr
INNER JOIN Produkter ON Tempo.produktnr = Produkter.Produktnr
)
SELECT CTE1.*, CTE2.Produktnamn
FROM CTE1
INNER JOIN CTE2 ON CTE2.SubID = FContainerPlats.id
ORDER BY FContainerPlats.HyllId, FContainerPlats.x, FContainerPlats.y