Select Top 1 Not Returning Correct Data 1

[TCARPENTER]

Sorry if this a noob question, or has been covered, but I haven't seen quite what I'm after - or I'm asking the wrong thing.

If I structure my query like this:

SELECT TOP 1 
	(SELECT TOP 1
	BDCComponentAttributes.Value
	FROM BDCComponentAttributes
	WHERE ComponentAttributeName = 'CfgDescription') AS CfgDescription,
	(SELECT TOP 1
	BDCComponentAttributes.Value
	FROM BDCComponentAttributes
	WHERE ComponentAttributeName = 'ConfiguredPrice') AS ConfiguredPrice,
	(SELECT TOP 1
	BDCComponentAttributes.Value
	FROM BDCComponentAttributes
	WHERE ComponentAttributeName = 'ImageLink') AS ImageLink,
	(SELECT TOP 1
	BDCComponentAttributes.Value
	FROM BDCComponentAttributes
	WHERE ComponentAttributeName = 'ArchDrop') AS ArchDrop,
	(SELECT TOP 1
	BDCComponentAttributes.Value
	FROM BDCComponentAttributes
	WHERE ComponentAttributeName = 'BottomPanel') AS BottomPanel,
	(SELECT TOP 1
	BDCComponentAttributes.Value
	FROM BDCComponentAttributes
	WHERE ComponentAttributeName = 'BottomRail') AS BottomRail,
	(SELECT TOP 1
	BDCComponentAttributes.Value
	FROM BDCComponentAttributes
	WHERE ComponentAttributeName = 'Dims') AS Dims,
	(SELECT TOP 1
	BDCComponentAttributes.Value
	FROM BDCComponentAttributes
	WHERE ComponentAttributeName = 'ExteriorTreatment') AS ExteriorTreatment,
	(SELECT TOP 1
	BDCComponentAttributes.Value
	FROM BDCComponentAttributes
	WHERE ComponentAttributeName = 'Handing') AS Handing,
	(SELECT TOP 1
	BDCComponentAttributes.Value
	FROM BDCComponentAttributes
	WHERE ComponentAttributeName = 'Height') AS Height,
	(SELECT TOP 1
	BDCComponentAttributes.Value
	FROM BDCComponentAttributes
	WHERE ComponentAttributeName = 'HingeNum') AS HingeNum,
	(SELECT TOP 1
	BDCComponentAttributes.Value
	FROM BDCComponentAttributes
	WHERE ComponentAttributeName = 'Hinges') AS Hinges,
	(SELECT TOP 1
	BDCComponentAttributes.Value
	FROM BDCComponentAttributes
	WHERE ComponentAttributeName = 'HingeSize') AS HingeSize,
	(SELECT TOP 1
	BDCComponentAttributes.Value
	FROM BDCComponentAttributes
	WHERE ComponentAttributeName = 'LockRail') AS LockRail,
	(SELECT TOP 1
	BDCComponentAttributes.Value
	FROM BDCComponentAttributes
	WHERE ComponentAttributeName = 'LockRailPosition') AS LockRailPosition,
	(SELECT TOP 1
	BDCComponentAttributes.Value
	FROM BDCComponentAttributes
	WHERE ComponentAttributeName = 'Panel') AS Panel,
	(SELECT TOP 1
	BDCComponentAttributes.Value
	FROM BDCComponentAttributes
	WHERE ComponentAttributeName = 'Profile') AS Profile,
	(SELECT TOP 1
	BDCComponentAttributes.Value
	FROM BDCComponentAttributes
	WHERE ComponentAttributeName = 'Radius') AS Radius,
	(SELECT TOP 1
	BDCComponentAttributes.Value
	FROM BDCComponentAttributes
	WHERE ComponentAttributeName = 'Species') AS Species,
	(SELECT TOP 1
	BDCComponentAttributes.Value
	FROM BDCComponentAttributes
	WHERE ComponentAttributeName = 'Stain') AS Stain,
	(SELECT TOP 1
	BDCComponentAttributes.Value
	FROM BDCComponentAttributes
	WHERE ComponentAttributeName = 'Stile') AS Stile,
	(SELECT TOP 1
	BDCComponentAttributes.Value
	FROM BDCComponentAttributes
	WHERE ComponentAttributeName = 'Style') AS Style,
	(SELECT TOP 1
	BDCComponentAttributes.Value
	FROM BDCComponentAttributes
	WHERE ComponentAttributeName = 'Thickness') AS Thickness,
	(SELECT TOP 1
	BDCComponentAttributes.Value
	FROM BDCComponentAttributes
	WHERE ComponentAttributeName = 'TopPanel') AS TopPanel,
	(SELECT TOP 1
	BDCComponentAttributes.Value
	FROM BDCComponentAttributes
	WHERE ComponentAttributeName = 'TopRail') AS TopRail,
	(SELECT TOP 1
	BDCComponentAttributes.Value
	FROM BDCComponentAttributes
	WHERE ComponentAttributeName = 'Width') AS Width,
	(SELECT TOP 1
	BDCComponentAttributes.Value
	FROM BDCComponentAttributes
	WHERE ComponentAttributeName = 'TopRailLocked') AS TopRailLocked
FROM  BDCComponentAttributes INNER JOIN
                      BDCComponents ON BDCComponentAttributes.ComponentID = BDCComponents.ComponentID INNER JOIN
                      BDCConfigurations ON BDCComponents.CfgID = BDCConfigurations.CfgID
WHERE  BDCConfigurations.ConfigurationID = '8492bb5c-52a1-4772-831f-dff54bf9a397'

I don't get the correct data for the where clause. If I structure it this way:

SELECT BDCComponentAttributes.ComponentAttributeName, BDCComponentAttributes.Value
FROM  BDCComponentAttributes INNER JOIN
                      BDCComponents ON BDCComponentAttributes.ComponentID = BDCComponents.ComponentID INNER JOIN
                      BDCConfigurations ON BDCComponents.CfgID = BDCConfigurations.CfgID
WHERE  BDCConfigurations.ConfigurationID =  '8492bb5c-52a1-4772-831f-dff54bf9a397'

I get the correct data, but I don't get field names for my report writers to easily apply to their report templates - what am I doing wrong?

TIA
Todd

 

[Qik3Coder]

It depends on how many fields you have. normally you manually Left JOIN to the tables for each field, or Case statement it out.

I notice you don't have any filtering criteria the inline select (observation, this may be intented)

I've got a bit of code i've been meaning to turn into a FAQ.
I came up with this to answer a question someone else had posted.
Use this as a base for your query.

CREATE TABLE myTestData(
[Year] int,
CRS_Code varchar(10),
School varchar(10),
Sub varchar(10),
prcnt money)


INSERT INTO myTestData
SELECT 2011, 'KT101', 'ML', 'English', .10 UNION ALL
SELECT 2011, 'KT101', 'ML', 'French', .60 UNION ALL
SELECT 2011, 'KT101', 'ML', 'German', .30 UNION ALL
SELECT 2011, 'KT101', 'LAW', 'English', .60 UNION ALL
SELECT 2011, 'KT101', 'LAW', 'French', .40 UNION ALL
SELECT 2011, 'KT102', 'ML', 'French', .50 UNION ALL
SELECT 2012, 'KT102', 'ML', 'French', .80 UNION ALL
SELECT 2013, 'KT102', 'ML', 'Japanese', .80 UNION ALL
SELECT 2013, 'KT102', 'JK', 'English', .20

DECLARE @_SQL varchar(8000), @int int
SELECT @_SQL = 'SELECT [Year], CRS_Code', @int = 0
SELECT  @_SQL = @_SQL + ', MAX(CASE WHEN t.School = ''' + tabs.School + ''' and t.SUB = ''' + 
	tabs.Sub + ''' THEN t.prcnt else 0 END) as ['+tabs.School+'-'+tabs.Sub +']' +char(13)
	FROM (Select School, SUB FROM (SELECT DISTINCT SUB FROM myTestData) as a
	CROSS JOIN (SELECT DISTINCT School FROM myTestData) as b) as Tabs
SELECT @_SQL = @_SQL + 'FROM myTestData t GROUP BY [YEAR], CRS_Code'

PRINT @_SQL

Exec(@_SQL)

Lodlaiden

You've got questions and source code. We want both!

 

[TCARPENTER]

Thanks Lodlaiden, that's what I needed!