I'm new to T-SQL, and having problems with the syntax of the query I want - this query shows all our customers who are receiving free service - I need to see the value of the free service - the service codes and the short description (ONLINE_DESC_ALA) are unique for those customers who get some free service. But the long description (STMT_DESCR_LINE1_ALA) is the same - Employees (EMP) get free service, so...

ServiceCode.......ONLINE_DESC_ALA.........STMT_DESCR_LINE1_ALA..........CHRG_ALA
......WE012...............Widget-EMP...........................Widget..............................$0.00
......WG055...............Basic Widget.........................Widget..............................$5.95

I want the large code block below to be my 'main' query, but also show in my record set ALL records where there is a match on the STMT_DESCR_LINE1_ALA, and the corresponding charge (CHRG_ALA)

is it possible to make the following query a SUB query, then query off of it, where the SubQuery.STMT_DESCR_LINE1_ALA = MainQuery.STMT_DESCR_LINE1_ALA ?

Ouch.

First of all, that's a really old syntax (like Oracle in 1999). Take out all the JOIN statements in the WHERE clause (tblA.Field1=tblB.FieldX) and put them in the JOIN clause where they belong. If you really need help, post the CREATE TABLE statement and enough INSERT statements to populate a table so the results can be checked. (If it's too hard to help you, nobody is going to do it, because it's too much... well, work!)

Mad, thanks - perhaps you missed:


haha - I don't even know how to create a table in T-SQL :-(

Quick answer is yes. You can use a query as a sub query.

Like Mad, however, I would have to ask where the code above came from as I've not seen that style since my informix days, some 10 years ago and even then its was somewhat out of date!

But by way of example, I think you need something in the following style:

SELECT *
FROM dbo.[Table] MQ
LEFT OUTER JOIN (
SELECT [Subquery]
FROM dbo.[Table] T
INNER JOIN dbo.[OtherTables] OT
ON T.[ID] = OT.[ID]
WHERE [Things] = 'Wasnames'
AND [Watchamacallits] = 'thingymagigs'
) SQ
ON MQ.[ID] = SQ.[ID]

This will give you everything from table "table", and the information from the subquery only where it matches

not sure about the [subquery] part of this

Probably a poor choice of example word. Enclosing something in [] indicates to SQL it is a column name. Therefore [Subquery] is just a column name. That said, I then went on to use [ID] in the join statement, not [Subquery], so my bad, sorry. This should make more sense:

SELECT *
FROM dbo.[Table] MQ
LEFT OUTER JOIN (

SELECT DISTINCT [ID]
FROM dbo.[Table] T
INNER JOIN dbo.[OtherTables] OT
ON T.[ID] = OT.[ID]
WHERE [Things] = 'Wasnames'
AND [Watchamacallits] = 'thingymagigs'
) SQ

ON MQ.[ID] = SQ.[ID]

*The distict just covers off the chance of the subquery returning multiple rows. Remove if that is intended behaviour