All we need is an easy explanation of the problem, so here it is.
I’m really confused by this SQL here, I have this sample where I am using the value from the main query in the subquery and then using XML PATH to add a comma between the dates. The setup is 2 tables one with the dates I want to string together. I am not sure why the subquery where statement is not retuning the correct results.
CREATE TABLE log ([logID] [int] NULL, [LogDate] [datetime] NULL ) ; CREATE TABLE logdata ([logdataID] [int] NULL, [logID] [datetime] NULL ) ; INSERT INTO log ([logID], [LogDate]) VALUES (1, 2021-02-01), (1, 2021-02-02), (1, 2021-02-03), (3, 2021-03-12), (4, 2021-02-12) ; INSERT INTO logdata ([logdataID], [logID]) VALUES (1, 1), (2, 2), (3, 3) ;
Select logID, logdataID LogDate = (SELECT ',' + CAST(LogDate AS varchar) FROM log WHERE logID = logID FOR XML PATH('') ) from logdata
1,2021-02-01,2021-02-02,2021-02-03,2021-03-12,2021-02-12 2,2021-02-01,2021-02-02,2021-02-03,2021-03-12,2021-02-12 3,2021-02-01,2021-02-02,2021-02-03,2021-03-12,2021-02-12
But my desired results would be:
1 2021-02-01,2021-02-02,2021-02-03 2 2021-03-12 3 2021-02-12
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
In your subquery,
WHERE logID = logID is ambiguous. LogID exists in two tables, but the query doesn’t tell SQL Server that you intend to use
logID from one table to join to the
logID in the other.
In your version of the query, SQL Server is interpreting that within the scope of the subquery only, so that it is using
log.logID = log.logID which is essentially saying
Instead, always use table aliases & prefix the columns with the table alias. Something like this:
SELECT ld.logID, ld.logdataID, LogDate = (SELECT ',' + CAST(l.LogDate AS varchar) FROM log AS l WHERE l.logID = ld.logID FOR XML PATH('') ) FROM #logdata AS ld;
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂