Count of rows from a joined table SQL Server

Question

Is it possible to make a count of rows (I am using a view) with a resultset of a subquery?

Assuming I have

Table1

t1id

Table2

id, t1id_Parent, t1id_Child

Table3

t1id

I query for

select
    t1.t1id as id,
    stuff((select ', ' + CAST(CASE WHEN t2.t1id_Child is not null THEN t2.t1id_Child ELSE '0' END AS varchar) from Table1 t where t.t1id_Parent = t1.t1id for xml path('')),1,2,'')  as ids
FROM
    Table1 as t1
LEFT OUTER JOIN 
    Table1 AS t2 ON t1.t1id = t2.t1id_Parent

So I have the needed ids combined into a string that looks like this '1,2,5,9' in the column named "ids" that is working correctly however what I need to do now is have this query

select
    t1.t1id as id,
    stuff((select ', ' + CAST(CASE WHEN t2.t1id_Child is not null THEN t2.t1id_Child ELSE '0' END AS varchar) from Table1 t where t.t1id_Parent = t1.t1id for xml path('')),1,2,'')  as ids
FROM
    Table1 as t1
LEFT OUTER JOIN 
    Table2 AS t2 ON t2.t1id_Parent = t1.t1id
LEFT OUTER JOIN 
    (select count(t1id) as rows_count, t1id 
     from Table3
     group by t1id) as docs ON docs.t1id in (ids)

to retrieve the row count from the 3rd table using the temporary column however I am getting an error of Invalid column name 'ids'

Is this possible or do I need to first run that query and then go back and run a new one for the count of the third table?

The result I need is

Table1.t1id, count(Table3.t1id)

Table3.t1id comes from Table1.t1id and all Table2.t1id_Child relation through Table1.t1id = Table2.t1id_Parent

I guess not possible, but got it partially working with CTE, I am now getting the list of say I have this '15673,15690,90987,45058' I can then do the query for the third column but getting the error

WITH items AS
(
select
a.ItemID,
stuff(
(select ',' +
CAST(
CASE WHEN b.[ChildItemID] is not null 
THEN b.[ChildItemID] ELSE 0 END AS varchar)
    from Table2 t2 
    where t2.[ParentItemID] = a.ItemID
    for xml path('')
),1,2,'')
select
items.*,
docs.docs_count
from items
LEFT OUTER JOIN (
select count([DocID]) as docs_count from Table3
group by DocID
) as docs ON docs.ItemID in (items.ItemID)

Conversion failed when converting the nvarchar value '15673,' to data type int.

Note that the issue is not concatenating, I am getting the result expected there, a comma separated list of values, the issue comes when I try to use the comma separated values in "as docs ON docs.ItemID in (items.ItemIDs)"

Why would you take data from multiple columns, concatenate it into a comma delimited string, and then try to join onto that string? Your join looks like a combination join + where statement, neither of which are complete. It might help to post what you expect your final result to look like. I would look into a `Common Table Expression (CTE)` or even just a `Table Variable` to accomplish what you want, and do away with the comma delimited string crap. You are taking what SQL server is good at, not using it, and then making it do something it isn't good at (i.e., string vs. relational data) — Dmitriy Khaykin, Jun 06 '16 at 18:24
Table2 has multiple records belonging to Table1 so I need all the ids without repeating rows from Table1, also I am displaying all ids in the user interface so I need those, without the concatenation I will end up with multiple records from Table1 which I don't want/need — Tlacaelel Ramon Luis, Jun 06 '16 at 18:41
It is not really clear what you are trying to do here. Perhaps this would be a good place to start. http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/ — Sean Lange, Jun 06 '16 at 18:42
It is not possible to use a column alias in a JOIN condition. I can tell you that much, most assuredly. — Tab Alleman, Jun 06 '16 at 18:44
One of the oddest queries I have seen. But I think this will make it shorter isnull(t2.t1id_Child, 0) — paparazzo, Jun 06 '16 at 18:54
Possible duplicate of [Custom aggregate function (concat) in SQL Server](http://stackoverflow.com/questions/4374709/custom-aggregate-function-concat-in-sql-server) — Ben, Jun 06 '16 at 19:21
@Ben Not really a duplicate as I am not asking how to concatenate but rather using the concatenated string in a "where in" statement — Tlacaelel Ramon Luis, Jun 06 '16 at 19:36
@Dmitriy Khaykin I used a combination of CTE and the comma delimited string and then used a function to convert the comma delimited string into a table to use into the "where in()" clause so unless there is a better way to achieve the same I will pick the CTE+function.as an answer — Tlacaelel Ramon Luis, Jun 06 '16 at 20:23
@TlacaelelRamonLuis so the logic is, you have a table, concat a string of ids, then use a function to put those values back into a table .... I think it can be done better by working with the values in table form, then concatenating the string at the end if that is part of a result that you need. — Dmitriy Khaykin, Jun 07 '16 at 16:51
It sounds like you got it working, but I think that it can be a bit less convoluted, that's all. Good work though on making it happen! — Dmitriy Khaykin, Jun 07 '16 at 16:53
@Dmitriy Khaykin I basically need a data set that has certain requirements, to achieve that I needed to query the DB then pull the ids and make another query for every record in the result-set so this is to make a single query and get all needed results with a single query, thought it would be faster but looks like it takes around the same amount of time. — Tlacaelel Ramon Luis, Jun 08 '16 at 22:35

Count of rows from a joined table SQL Server

0 Answers0