asked on
create query in sql server
I am having trouble creating an sql server query that uses a sub query.
I have a sub query :
The main query (ive restricted it with the where for the moment)
Not sure how to port this over to sql server with the sub query despite reading one of the experts posts on access to sql server
I have a sub query :
SELECT TblWordTags.ClientCode, TblWordTags.ClientCodeWordPosition
FROM TblWordTags
GROUP BY TblWordTags.ClientCode, TblWordTags.ClientCodeWordPosition;
The main query (ive restricted it with the where for the moment)
SELECT TblWords.ClientCodeWordPosition, TblWords.ClientCode, TblWords.Word, QrySubQuery.ClientCode, TblWords.StrFull
FROM TblWords LEFT JOIN QrySubQuery ON (TblWords.ClientCodeWordPosition = QrySubQuery.ClientCodeWordPosition) AND (TblWords.ClientCode = QrySubQuery.ClientCode)
WHERE (((TblWords.ClientCode)="A01ND"));
Not sure how to port this over to sql server with the sub query despite reading one of the experts posts on access to sql server
Microsoft SQL Server
Last Comment
ste5an
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
if i save the subquery then the outer query can use it or do i need to do something else?
ASKER
I wasnt sure sql server worked in this way, but it does so thank you
I would use a CTE:
WITH QrySubQuery
AS ( SELECT WT.ClientCode ,
WT.ClientCodeWordPosition
FROM TblWordTags WT
GROUP BY WT.ClientCode ,
WT.ClientCodeWordPosition
)
SELECT W.ClientCodeWordPosition ,
W.ClientCode ,
W.Word ,
Q.ClientCode ,
W.StrFull
FROM TblWords W
LEFT JOIN QrySubQuery Q ON ( W.ClientCodeWordPosition = Q.ClientCodeWordPosition
AND W.ClientCode = Q.ClientCode
)
WHERE W.ClientCode = 'A01ND';
apart from that, what are the issues?