Quote:
|
Originally Posted by bfp
In this example it's MSSQL...but ideally I'm looking for ANSI cos it won't always be MSSQL. But I am prepare to use DB specifics if required (as long as there's a solution for MSSQL, DB2, Oracle...maybe MYSQL).
|
the database systems you mentioned vary wildly in their implementation of standard SQL concatenation
let's use the plus sign for now, which MSSQL likes but the others don't (see my remark in post #2
)
Code:
SELECT A.ID
, A.SubTotal
, B.CSEQS
, A.ID + B.CSEQS AS myconcat
, C.ID
, C.TranAmount
FROM A
LEFT OUTER
JOIN B
ON B.ID = A.ID
LEFT OUTER
JOIN C
ON C.ID = A.ID + B.CSEQS
ID SubTotal CSEQS myconcat ID TranAmount
------ --------- ----- --------- --------- -----------
12345 150.00 01 1234501 1234501 50.00
12345 150.00 02 1234502 (null) (null)
12346 200.00 (null) (null) (null) (null)
12347 101.00 (null) (null) (null) (null)
is this the correct data?