what's the difference of this two code?
I got all NULL on the First code
And the Second one is OK
USE DataEntry_Test
SELECT TOP 100 A.Ref_Category,A.Ref_att01,
(SELECT C.ATV_Attribute_Value_Description
WHERE B.ref_cat_att1=C.ATV_Attribute_Type
AND A.Ref_att01=C.ATV_Attribute_Value
AND A.Ref_Category= B.Ref_Category
AND A.Ref_Category= C.ATV_Category)AS ATV01
FROM dbo.reference AS A,dbo.Category_Attribute AS B,
dbo.Attribute_Type_Value AS C
USE DataEntry_Test
SELECT TOP 100 A.Ref_Category,A.Ref_att01,
C.ATV_Attribute_Value_Description AS ATV01
FROM dbo.reference AS A,dbo.Category_Attribute AS B,
dbo.Attribute_Type_Value AS C
WHERE B.ref_cat_att1=C.ATV_Attribute_Type
AND A.Ref_att01=C.ATV_Attribute_Value
AND A.Ref_Category= B.Ref_Category
AND A.Ref_Category= C.ATV_Category
因為第一個SQL資料,是 A表 * B表 * C表
所以會有Null資料交集不到~
SELECT TOP 100 A.Ref_Category
,A.Ref_att01
,(
SELECT C.ATV_Attribute_Value_Description
WHERE B.ref_cat_att1 = C.ATV_Attribute_Type
AND A.Ref_att01 = C.ATV_Attribute_Value
AND A.Ref_Category = B.Ref_Category
AND A.Ref_Category = C.ATV_Category
) AS ATV01
FROM dbo.reference AS A
,dbo.Category_Attribute AS B
,dbo.Attribute_Type_Value AS C
第二個SQL資料,是 A表 * B表 * C表 並指定交集資料~
所以一定沒有Null問題~
SELECT TOP 100 A.Ref_Category
,A.Ref_att01,
C.ATV_Attribute_Value_Description AS ATV01
FROM dbo.reference AS A
,dbo.Category_Attribute AS B
,dbo.Attribute_Type_Value AS C
WHERE B.ref_cat_att1 = C.ATV_Attribute_Type
AND A.Ref_att01 =C.ATV_Attribute_Value
AND A.Ref_Category = B.Ref_Category
AND A.Ref_Category = C.ATV_Category
重點:有無 Where 的篩選
Thank you
and I still need to create a column like this,
How can I fix it? without NULL
USE DataEntry_Test
SELECT TOP 100 A.Ref_Category,A.Ref_att01,
(SELECT C.ATV_Attribute_Value_Description
WHERE B.ref_cat_att1=C.ATV_Attribute_Type
AND A.Ref_att01=C.ATV_Attribute_Value
AND A.Ref_Category= B.Ref_Category
AND A.Ref_Category= C.ATV_Category)AS ATV01,
(SELECT C.ATV_Attribute_Value_Description
WHERE B.ref_cat_att2=C.ATV_Attribute_Type
AND A.Ref_att02=C.ATV_Attribute_Value
AND A.Ref_Category= B.Ref_Category
AND A.Ref_Category= C.ATV_Category)AS ATV02
FROM dbo.reference AS A,dbo.Category_Attribute AS B,dbo.Attribute_Type_Value AS C
因為你只有用From
還要加上Where
否則一定會有null
例如以下篩選
FROM dbo.reference AS A
,dbo.Category_Attribute AS B
,dbo.Attribute_Type_Value AS C
WHERE .......
另外會有null
是因為那些欄位的值會變成null
WHERE B.ref_cat_att2 = C.ATV_Attribute_Type
AND A.Ref_att02 = C.ATV_Attribute_Value
AND A.Ref_Category = B.Ref_Category
AND A.Ref_Category = C.ATV_Category
--在某些交集的資料,會變成以下這樣~
WHERE B.ref_cat_att2 = null
AND A.Ref_att02 = null
AND A.Ref_Category = null
AND A.Ref_Category = null
連帶查詢的資料也會null
Thank you so very much