Using Sub Query for results in a column

Transact-SQL
1

Hi There,
GBritton kindly helped me with the Sql shown below and in isolation works fine.
I need to get the result of the script into a column of my main query.

SELECT t2.*
 , CASE 
 WHEN t2.TypeID = 4
 AND t2.CreateDate < ALL (
 SELECT t2.CreateDate
 FROM tbTable2 t2
 WHERE t2.Table1ID = t1.AutoID
 AND t2.TypeID = 1
 AND t2.CreateDate IS NOT NULL
 )
 THEN 'Y'
 END AS Exceeded
FROM tbTable1 t1
JOIN tbTable2 t2 ON t1.AutoID = t2.Table1ID

I am unable to get the result into a column of my main query.
My main query is returning the rows that I need and I have a column called "ArcId" returned by the main query. I would like to use the value in this column to get the "Exceeded" value from the Sql script above.

Would anyone know how I could do that.

Thanks for the help.

Best Regards,

Steve.

2

You would join your main query to what you just posted, formulated as a subquery or cte

3

Hi Gerald,
Thanks for the quick reply again.
I'm not sure what you mean.
My Main Query brings back a column called "ArcId' with say a value of 45.
How do I add the query you gave to me, using the "ArcId" value in a sub query?

I know it's easy when you know how but I'm stumped. I worked on this all over the weekend and still got nowhere.
Thanks again for the help, it's very appreciated.

Best Regards,

Steve.

4 
select ..., sub.Exceeded
from ... AS main
join (
...subquery -- your query that you posted
) sub
on main.yourcolumn = sub.yourcolumn
5

Hi Gerald,
That almost worked but it repeats the row for every record in my sub query.
I think it's something to do with the piece of code:
WHEN t2.TypeID = 4
AND t2.CreateDate < ALL (

If there are 5 records in the t2 table I am getting the main record repeated 5 times.

Best Regards,

Steve.

6

Well, yes you would get repeats. you could change the subquery to only return the join column and Exceeded and use DISTINCT.

7

Hi Gerald,

Using distinct does not change the returned records.

Best Regards,

Steve.

8

In that case, your join column is not unique in the target table. Think about it.

9

Hi Gerald,

The field is unique.
The thing is if there are say 9 records in the join table when I use Distinct it reduces to 2 records and the only field that is different is "Exceeded" One of the records has 'Y' and the other has 'null'

Best Regards,

Steve.

10

OK -- sounds right. So add a where clause (WHERE Exceeded = 'Y')