Help with select

[WolfShade]

Hello, everyone.

Using the same tables in my previous post ..

I'm trying to build a select that will first list stand-alone baselines (no project); next, baselines that have projects and the associated projects; next are stand-alone projects (no baseline associated.)

But it's not really THAT simple.

The select options should be something like:

Baseline 1234
Baseline 1235
Baseline 1346
Baseline 1421
    --------- (greyed out/disabled)
Baseline 1211
   Project 1 (1211)
   Project 3 (1211)
Baseline 1387
   Project 2 (1387)
   Project 5 (1387)
Baseline 1455
   Project 12 (1455)
    --------- (greyed out/disabled)
Project 10
Project 11
Project 13

What I have, so far, is a SP that creates a temp table that has an IDENTITY, makes a select for stand-alone baselines and inserts that into the temp table, makes a select for stand-alone projects and inserts that into the temp table, then does the select from the temp table ORDER BY the IDENTITY. I'm not sure how to get the middle SELECT to do what I'm looking for.

Any suggestions?

Thanks,

[ssystems]

Subquery and Union

[WolfShade]

Can you be a bit more specific? How do I get a baseline, then select all projects associated with that baseline? Is there some kind of for..each loop, or something?

[WolfShade]

Anyone?

[WolfShade]

Can anyone be more specific than subquery and union?

[eval(BadCode)]

What flavor of SQL are you using?

Also- show us the tables involved and their fields, and possibly an example of the data you have and the result you want.

[WolfShade]

The flavour is MS SQL Server 2008. The two tables are BASELINE and PROJECT, and both have way too many columns to list, but I'll see if I can give an idea using the columns that I need.

BASELINE
-BASELINE_ID int identity(1,1) PK,
-APP_ID int


PROJECT
-PROJ_ID int identity(1,1) PK,
-APP_ID int,
-PROJECT_ID int,
-BASELINE_ID int FK

If a project is associated with a baseline, that project will have that baseline id in BASELINE_ID; if it is NOT associated with a baseline, BASELINE_ID will be NULL.

The query should get all baselines that are not associated with a project; followed by baselines that are associated with project(s), and the related projects per baseline; followed by projects not associated with a baseline.

I am currently working on a SP that comes close to what I need. But the way it currently exists, I cannot use a nice ColdFusion function of CFOUTPUT called "GROUP", because any baseline that does not have a project associated with it will output a blank line in between them. So I'm hoping to have a query that will put all information in the same columns, so I can do just a plain CFOUTPUT (not to mention, slightly less data means slightly less bandwidth in the network.)

I'll put that SP as it currently exists, here:

DECLARE @appid int
SET @appid = 10992

CREATE TABLE #thisTempTable (
	optDisplay varchar(100), 
	optValue varchar(100), 
	appName varchar(500),  
	so int IDENTITY
	)

INSERT INTO #thisTempTable(optDisplay, optValue, appName)
SELECT 'Baseline ' + CAST([ab].[BASELINE_ID] as varchar(10)) as optDisplay,
	CAST([ab].[BASELINE_ID] as varchar(10)) + '|BASELINE' as optValue, a.[APP_NAME] as appName
  FROM [BASELINE] ab
		JOIN [APP] a ON a.APP_ID = ab.APP_ID
 WHERE [ab].[APP_ID] = @appid AND [a].[CURRENT_IND] = 'Y' 
		AND [ab].[BASELINE_ID] NOT IN (
				SELECT DISTINCT [BASELINE_ID] 
				  FROM [PROJECT]
				 WHERE [APP_ID] = @appid 
						AND [BASELINE_ID] IS NOT NULL 
						AND [BASELINE_ID] > 0
									  )

INSERT into #thisTempTable(optDisplay, optValue, appName)
SELECT DISTINCT 'Baseline ' + CAST([ab].[BASELINE_ID] as varchar(10)) as optDisplay,
	CAST([ab].[BASELINE_ID] as varchar(10)) + '|BASELINE' as optValue, a.[APP_NAME] as appName
  FROM [BASELINE] ab
		JOIN [APP] a ON a.APP_ID = ab.APP_ID
		JOIN [PROJECT] ap ON ap.[BASELINE_ID] = ab.[BASELINE_ID]
 WHERE [ab].[APP_ID] = @appid AND [a].[CURRENT_IND] = 'Y' 

INSERT INTO #thisTempTable(optDisplay, optValue, appName)
SELECT 'Project ' + CAST([ap].[PROJ_ID] as varchar(10)) as optDisplay,
	CAST([ap].[PROJ_ID] as varchar(10)) + '|PROJECT' as optValue, a.[APP_NAME] as appName
  FROM [PROJECT] ap 
		JOIN [APP] a ON a.APP_ID = ap.APP_ID
 WHERE [ap].[APP_ID] = @appid AND [a].[CURRENT_IND] = 'Y' 
		AND [ap].[BASELINE_ID] IS NULL 


SELECT *
  FROM #thisTempTable
ORDER BY so


/*
Desired format:

[optDisplay]	[optValue]		[appName]				[so]
--------------------------------------------------------------
Baseline 9310	9310|BASELINE	Application Alpha	1
Baseline 9313	9313|BASELINE	Application Alpha	2
Baseline 9314	9314|BASELINE	Application Alpha	3
Baseline 9317	9317|BASELINE	Application Alpha	4
Baseline 9318	9318|BASELINE	Application Alpha	5
Baseline 9322	9322|BASELINE	Application Alpha	6
Baseline 9323	9323|BASELINE	Application Alpha	7
Baseline 9324	9324|BASELINE	Application Alpha	8
Baseline 9312	9312|BASELINE	Application Alpha	9
- Project 3	3|PROJECT	Application Alpha	10
Baseline 9315	9315|BASELINE	Application Alpha	11
- Project 6	6|PROJECT	Application Alpha	12
- Project 7	7|PROJECT	Application Alpha	13
- Project 8	8|PROJECT	Application Alpha	14
Project 1	1|PROJECT	Application Alpha	15
Project 2	2|PROJECT	Application Alpha	16
*/