iT邦幫忙

0

SQL question

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

2 個回答

2
純真的人
iT邦高手 1 級 ‧ 2018-10-08 13:16:37
最佳解答

因為第一個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 的篩選

po114477 iT邦新手 5 級 ‧ 2018-10-08 14:09:25 檢舉

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

po114477 iT邦新手 5 級 ‧ 2018-10-08 15:22:08 檢舉

Thank you so very much

我要發表回答

立即登入回答