www.webdeveloper.com
Results 1 to 7 of 7

Thread: Help with select

  1. #1
    Join Date
    Dec 2002
    Location
    St. Louis, MO, USA
    Posts
    1,582

    Help with select

    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:
    Code:
    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,
    |||||
    o . Q
    ___ "You live and you learn; or you don't live long." - Lazarus Long

    ^_^

  2. #2
    Join Date
    Oct 2009
    Posts
    658
    Subquery and Union

  3. #3
    Join Date
    Dec 2002
    Location
    St. Louis, MO, USA
    Posts
    1,582
    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?
    |||||
    o . Q
    ___ "You live and you learn; or you don't live long." - Lazarus Long

    ^_^

  4. #4
    Join Date
    Dec 2002
    Location
    St. Louis, MO, USA
    Posts
    1,582
    Anyone?
    |||||
    o . Q
    ___ "You live and you learn; or you don't live long." - Lazarus Long

    ^_^

  5. #5
    Join Date
    Dec 2002
    Location
    St. Louis, MO, USA
    Posts
    1,582
    Can anyone be more specific than subquery and union?
    |||||
    o . Q
    ___ "You live and you learn; or you don't live long." - Lazarus Long

    ^_^

  6. #6
    Join Date
    Jul 2010
    Location
    /ramdisk/
    Posts
    865
    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.
    I use (, ; : -) as I please- instead of learning the English language specification: I decided to learn Scheme and Java;

  7. #7
    Join Date
    Dec 2002
    Location
    St. Louis, MO, USA
    Posts
    1,582
    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:
    Code:
    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
    */
    |||||
    o . Q
    ___ "You live and you learn; or you don't live long." - Lazarus Long

    ^_^

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles