Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2017
    Posts
    2

    Unanswered: join 3 separate Selects with Wheres

    I have inherited some processes and wanted to make them more efficient. One way for me to do that is to improve the SQL code. I am not very proficient with joins and needed some advice on how best to structure my query. Each select has its own criteria. An example of the current code is below. any advice is appreciated.

    Code:
    Data:
    SELECT 
    	ID,       
    	DATE,  
    	MAX(STATUS)  AS STATUS
    FROM TABLE1
    WHERE (DATE>'11/29/2006')
    GROUP BY 
    	ID, 
    	DATE;
    
    LEFT JOIN (Data) 
    SELECT 
    	ID,
        	DATE,
        	STATUS_TYPE,
        	COUNT
    FROM TABLE2
    WHERE STATUS_TYPE IN ('020','026','032'); 
    
    LEFT JOIN (Data) 
    SELECT
    	ID,
        	DATE,
    	PD_DATE,
    	DAYS(CURRENT DATE) - DAYS(PD_DATE) AS AGE
    FROM TABLE3 
    WHERE (DUE_DATE > '11/29/2006');

    Is it as simple as joining the tables and using where X and Y and Z?

  2. #2
    Join Date
    Oct 2007
    Posts
    156
    Provided Answers: 9
    you can use UNION to put disparate data together, but the number of columns and the datatypes have to be the same for each column in order of selection.

  3. #3
    Join Date
    Sep 2017
    Posts
    2
    I truly want to Left join the data.

    Maybe a little more data will provide needed details.
    I use Qlikview to gather the data and having 3 separate Select statements and doing the joins in Qlikview is less efficient than running a single joined SQL Select statement. Does that make sense?

  4. #4
    Join Date
    Oct 2007
    Posts
    156
    Provided Answers: 9
    yes, it does. And, as I mentioned previously, you can do so with the use of UNION. Take a look at it. As already, mentioned you will have to change your select statement so that you have the same number of columns being returned in each and the data type has to be match for each column.

    Code:
    SELECT 
    	ID,       
    	DATE,  
    	MAX(STATUS)  AS STATUS
    FROM TABLE1
    WHERE (DATE>'11/29/2006')
    GROUP BY 
    	ID, 
    	DATE;
    union
    SELECT 
    	ID,
        	DATE,
        	STATUS_TYPE,
        	COUNT
    FROM TABLE2
    WHERE STATUS_TYPE IN ('020','026','032'); 
    
    union
    SELECT
    	ID,
        	DATE,
    	PD_DATE,
    	DAYS(CURRENT DATE) - DAYS(PD_DATE) AS AGE
    FROM TABLE3 
    WHERE (DUE_DATE > '11/29/2006');

  5. #5
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    577
    Provided Answers: 3
    You can also try something like:
    SELECT A.ID, A.DATE, A.STATUS
    FROM
    (
    your_first_select
    ) A
    LEFT JOIN
    (
    you_second_select
    ) B
    ON A.DATA = B.DATA
    LEFT JOIN
    (
    your_third_select
    ) C
    ON A.DATA = C.DATA
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  6. #6
    Join Date
    Oct 2007
    Posts
    156
    Provided Answers: 9
    except for the fact that it is disparate data and may not have a key to join on and what would be done with the data that has no corresponding in the first table? The only way to get this in a single query would be with three unions selecting the same number of columns with the same data types. I would suggest casting most of the fields as CHAR, to ensure compatability between them.

Posting Permissions

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