SQLTeam.com | Weblogs | Forums

Using Sub Query for results in a column

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.

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

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.

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

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.

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

Hi Gerald,

Using distinct does not change the returned records.

Best Regards,

Steve.

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

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.

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