1

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.

http://www.sqlfiddle.com/#!5/5443b/2

Setup

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)
;

My attempt:

Select 
logID,
logdataID
LogDate =  (SELECT ',' + CAST(LogDate AS varchar) FROM log WHERE logID =  logID FOR XML PATH('') )
from logdata

Results

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
4
  • You forget to add GROUP BY. PS. WHERE logID = logID - seriously? this is the most exotic method to check for NULL...
    – Akina
    Mar 9, 2022 at 16:22
  • Have you considered using STRING_AGG?
    – mustaccio
    Mar 9, 2022 at 16:25
  • You tag the question with [sql-server]. But the fiddle sets SQLite as DBMS... Why?
    – Akina
    Mar 9, 2022 at 16:26
  • But my desired results would be: ?? The row (2, 2) in logdata have no matched row in log.
    – Akina
    Mar 9, 2022 at 16:32

1 Answer 1

6

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 WHERE 1=1.

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.