How to check the Multiple Left Join

SQL Server Administration
1

Hi,

I need to check the existing SQL Query and they using Left Join with multiple tables.

The expected result is some value but i am getting NULL, So how i need check the left join query result or how to separte the SQL Query .

Example:

Select
a.ID as ID
b.Name as Name
c.Value as Value
d. hours as Hours
e.meter as Meter

From

Employee a with (NOLOCK)
LEFT JOIN
Sale b with (NOLOCK)
ON
a.id = b.id
LEFT JOIN
Classrecord c with (NOLOCK)
ON
c.Id = '123456'
LEFT JOIN
Datarecord d with (NOLOCK)
ON
d.id = c.id
LEFT JOIN
Orderrecord e with (NOLOCK)
ON
c.ID = d.ID

Where PriceID = '65478ADFEH'

how to check the Expected results or debug in multiple left Join.

please help

2

Please post DDl and sample data in the form of inserts

3

I hate to see that in code. Most people have no idea how dangerous it is ...

You will, sooner or later, get some rows (i.e. rows which have existed in the database for some time, not new rows that are "being inserted now") MISSING from the results, or DUPLICATED twice in the results. This has nothing to do with dirty rows that are included in your query but subsequently rolled back (which may not important to queries such as this). So if you were not aware of that please take note.

4

Table --> Classrecord_C

NumValue
575.000000
320.000000
240.000000
480.000000
400.000000

Table -- Price_hours_c

Base_Abs
34.98
32.18
32.18
34.98

Expected result:

xSLBaseHours
34.98
32.18
32.18
34.98

SQL Query:
SELECT SLphc.Base_Abs as xSLBaseHours
FROM dbo.Price_C pd with (NOLOCK)
LEFT JOIN dbo.PriceValue_C pdv with (NOLOCK)

ON 	pdv.SPDGUID=pd.camosguid AND pd.AttributeIdent = 'Load' and pdv.flag_Activated = 1 AND	pdv.Deleted = 0 

LEFT JOIN dbo.ClassRecord_C cr  with (NOLOCK)
ON
	cr.PriceListGUID = 'DE3BC35104E7A64F9A168A19C80ED84A' AND
	cr.ClassName = 'ComSpace' AND
	cr.Deleted = 0 AND
	cr.Attribute = 'Load' AND
	((pd.AttributeType='ASCII' and cr.CharValue = pdv.AttributeValue) OR
		(pd.AttributeType='number' and
		cr.NumValue = 
			case isnumeric(pdv.AttributeValue) 
				when 1 then convert(decimal(18,6),replace(replace(pdv.AttributeValue,' ',''),',','.')) 
				else null 
			end
		)
	)

LEFT JOIN
dbo.pricelist_price_c plc with (NOLOCK)
ON
plc.ClassRecordGUID = cr.CGUID AND
plc.Deleted = 0
LEFT JOIN
dbo.Price_Hour_C plhc with (NOLOCK)
ON
plhc.ClassRecordGUID = cr.CGUID AND
plhc.Deleted = 0

	LEFT JOIN
		dbo.pricelist_price_c SLplc with (NOLOCK)
	ON 
		SLplc.Deleted = 0 and
		SLplc.ClassRecordGUID in (
			SELECT SLcr.CGUID
				FROM 
					dbo.Price_C SLpd with (NOLOCK)
				LEFT JOIN
					dbo.PriceValue_C SLpdv  with (NOLOCK)
				ON
					SLpdv.SPDGUID=SLpd.Cguid AND
					SLpdv.flag_Activated = 1 AND
					SLpdv.Deleted = 0 
				LEFT JOIN
					dbo.ClassRecord_C SLcr  with (NOLOCK)
				ON
					SLcr.PriceListGUID = '762F693052B72847A8AA48A4FFF7A201' AND
					SLcr.ClassName = slpd.AttributeClass AND
					slpd.AttributeClass = 'ComSpace' AND
					SLcr.Deleted = 0 AND
					SLcr.Attribute = slpd.AttributeIdent AND
					slpd.AttributeIdent = 'Load' AND
					slpdv.AttributeValue = pdv.AttributeValue AND
					(
						(SLpd.AttributeType='ASCII' and SLcr.CharValue = SLpdv.AttributeValue) OR
						(SLpd.AttributeType='number' and
							SLcr.NumValue = 
								case isnumeric(SLpdv.AttributeValue) 
									when 1 then convert(decimal(18,6),replace(replace(SLpdv.AttributeValue,' ',''),',','.')) 
									else null 
							end
							)
				)
				WHERE
					SLpd.SPGUID='4574A2DB26C5A74B824FA84C2687C6E3' AND 
					SLpd.flag_Activated = 1 AND
					SLpd.Deleted = 0 AND
					SLpd.AttributeIdent <> '' AND
					SLPD.flag_SP = 1 AND
					(slpd.flag_TP=1 or slpd.flag_ID=1)
		)

LEFT JOIN
dbo.Price_C sslpd with (NOLOCK)
ON
pd.AttributeClass = sslpd.AttributeClass AND
sslpd.Deleted = 0 AND
pd.AttributeIdent = sslpd.AttributeIdent AND
pd.AttributeType = sslpd.AttributeType AND
sslpd.SPGUID='4574A2DB26C5A74B824FA84C2687C6E3' AND
sslpd.flag_Activated = 1

	LEFT JOIN
		dbo.pricelist_hour_c SLphc with (NOLOCK)
	ON 
		SLphc.Deleted = 0 and
		SLphc.ClassRecordGUID in (
			SELECT SLhcr.camosGUID
				FROM 
					dbo.Price_C SLhpd with (NOLOCK)
				LEFT JOIN
					dbo.PriceValue_C SLhpdv  with (NOLOCK)
				ON
					SLhpdv.SPDGUID=SLhpd.Cguid AND
					SLhpdv.flag_Activated = 1 AND
					SLhpdv.Deleted = 0 
				LEFT JOIN
					dbo.ClassRecord_C SLhcr  with (NOLOCK)
				ON
					SLhcr.PriceListGUID = '762F693052B72847A8AA48A4FFF7A201' AND
					SLhcr.ClassName = slhpd.AttributeClass AND
					slhpd.AttributeClass = 'ComSpace' AND
					SLhcr.Deleted = 0 AND
					SLhcr.Attribute = slhpd.AttributeIdent AND
					slhpd.AttributeIdent = 'Load' AND
					slhpdv.AttributeValue = pdv.AttributeValue AND
					(SLhcr.CharValue = slhpdv.AttributeValue OR
					CONVERT(nvarchar,SLhcr.NumValue) = SLhpdv.AttributeValue)
				WHERE
					SLhpd.SPGUID='4574A2DB26C5A74B824FA84C2687C6E3' AND 
					SLhpd.flag_Activated = 1 AND
					SLhpd.Deleted = 0 AND
					SLhpd.AttributeIdent <> '' AND
					SLhPD.flag_SP = 1 AND
					(slhpd.flag_TP=1 or slhpd.flag_ID=1)
		
			)
WHERE
	pd.SPGUID='B207F062320D46469DAC5FE420ABC090' AND

	
(
	plc.Base_Abs <> 0 or
	plc.Service_Abs <> 0 or
	plc.Meter_Abs <> 0 or
	plc.Each_Abs <> 0 or
	plhc.Base_Abs <> 0 or
	plhc.Service_Abs <> 0 or
	plhc.Meter_Abs <> 0 
) AND 

	pd.flag_Activated = 1 AND
	pd.Deleted = 0 AND
	pd.AttributeIdent <> '' 
ORDER BY
	pd.AttributeClass,pd.AttributeIdent,pdv.AttributeValue