none
SQL Join two tables - ON a one to many fields based on a value

    Question

  • Hi,

    I am trying to join a table to view in sql to see the most recent date a code was used:

    The first table has contains all the code to checkk for a date and is as follows:

    CREATE TABLE [dbo].[SSRFANV](
    	[SUN_DB] [char](3) NOT NULL,
    	[CATEGORY] [char](2) NOT NULL,
    	[CODE] [char](15) NOT NULL,
    	[LOOKUP] [char](10) NOT NULL,
    	[UPDATED] [char](8) NOT NULL,
    	[NAME] [char](30) NOT NULL,
    	[PROHB_POST] [char](1) NOT NULL,
    	[BUDGET_CHECK] [char](1) NOT NULL,
    	[BUDGET_STOP] [char](1) NOT NULL,
    	[DATA_1] [char](5) NOT NULL
    ) ON [PRIMARY]
    
    
    The view contains all the transactions, which come from a various tables as below:
    
    <pre lang="x-sql">CREATE VIEW [dbo].[AllGroupProfit] AS
    SELECT *, 'dbo.SALFLDGS21' AS 'TABLE_NAME'
    FROM SALFLDGS21
    UNION ALL
    SELECT *, 'dbo.SALFLDGS22' AS 'TABLE_NAME'
    FROM SALFLDGS22
    UNION ALL
    SELECT *, 'dbo.SALFLDGS24' AS 'TABLE_NAME'
    FROM SALFLDGS24
    UNION ALL
    SELECT *, 'dbo.SALFLDGS25' AS 'TABLE_NAME'
    FROM SALFLDGS25
    
    And so on and so on... Each these tables are identical and formed as such:
    <pre lang="x-sql">CREATE TABLE [dbo].[SALFLDGS48](
    	[ACCNT_CODE] [char](15) NOT NULL,
    	[PERIOD] [int] NOT NULL,
    	[TRANS_DATE] [int] NOT NULL,
    	[JRNAL_NO] [int] NOT NULL,
    	[JRNAL_LINE] [int] NOT NULL,
    	[AMOUNT] [numeric](18, 3) NOT NULL,
    	[D_C] [char](1) NOT NULL,
    	[ALLOCATION] [char](1) NOT NULL,
    	[JRNAL_TYPE] [char](5) NOT NULL,
    	[JRNAL_SRCE] [char](5) NOT NULL,
    	[TREFERENCE] [char](15) NOT NULL,
    	[DESCRIPTN] [char](25) NOT NULL,
    	[ENTRY_DATE] [int] NOT NULL,
    	[ENTRY_PRD] [int] NOT NULL,
    	[DUE_DATE] [int] NOT NULL,
    	[ALLOC_REF] [int] NOT NULL,
    	[ALLOC_DATE] [int] NOT NULL,
    	[ALLOC_PERIOD] [int] NOT NULL,
    	[ASSET_IND] [char](1) NOT NULL,
    	[ASSET_CODE] [char](10) NOT NULL,
    	[ASSET_SUB] [char](5) NOT NULL,
    	[CONV_CODE] [char](5) NOT NULL,
    	[CONV_RATE] [numeric](18, 9) NOT NULL,
    	[OTHER_AMT] [numeric](18, 3) NOT NULL,
    	[OTHER_DP] [char](1) NOT NULL,
    	[CLEARDOWN] [char](5) NOT NULL,
    	[REVERSAL] [char](1) NOT NULL,
    	[LOSS_GAIN] [char](1) NOT NULL,
    	[ROUGH_FLAG] [char](1) NOT NULL,
    	[IN_USE_FLAG] [char](1) NOT NULL,
    	[ANAL_T0] [char](15) NOT NULL,
    	[ANAL_T1] [char](15) NOT NULL,
    	[ANAL_T2] [char](15) NOT NULL,
    	[ANAL_T3] [char](15) NOT NULL,
    	[ANAL_T4] [char](15) NOT NULL,
    	[ANAL_T5] [char](15) NOT NULL,
    	[ANAL_T6] [char](15) NOT NULL,
    	[ANAL_T7] [char](15) NOT NULL,
    	[ANAL_T8] [char](15) NOT NULL,
    	[ANAL_T9] [char](15) NOT NULL,
    	[POSTING_DATE] [int] NOT NULL,
    	[ALLOC_IN_PROGRESS] [char](1) NOT NULL,
    	[HOLD_REF] [int] NOT NULL,
    	[HOLD_OP_ID] [char](3) NOT NULL
    ) ON [PRIMARY]
    
    My sql query so far is as follows:
    
    <pre lang="x-sql">SELECT T.SUN_DB, T.CATEGORY, T.CODE, 'ANAL_'+T.CATEGORY AS 'Tran Anal Code', T.NAME, T.PROHB_POST, MAX(GP.TRANS_DATE), COUNT(*)
    FROM SSRFANV AS T 
    LEFT JOIN dbo.AllGroupProfit AS GP ON T.SUN_DB = RIGHT(GP.TABLE_NAME,3) AND T.CODE =...
    

    I am now stuck about how to join the tables as depending on the value from T.CATEGORY The table needs to be joined to a different field from The view.

    E.g If T.CATEGORY = "T1" then THE value in T.CODE needs to join field ANAL_T1. If CATEGORY = "T2" then a join to ANAL_T2 is needed etc etc... I am not sure how to do this, if it is possible, or if there is a better way of doing this I would very much be interested in knowing how to go about it.

    As you can see from the query I am creating a 'Tran Anal Code' which then identifies where to join to but I'm not sure if that is of any other use.

    Any help is greatly received.

    Russell


    Russell Durrant
    Friday, May 27, 2011 10:40 AM

Answers

  • Try the following:

    SELECT T.SUN_DB, T.CATEGORY, T.CODE, 'ANAL_'+T.CATEGORY AS 'Tran Anal Code', T.NAME, T.PROHB_POST, MAX(GP.TRANS_DATE), COUNT(*)
    
    FROM SSRFANV AS T 
    
    LEFT JOIN dbo.AllGroupProfit AS GP
    
    ON T.SUN_DB = RIGHT(GP.TABLE_NAME,3)
    
    AND T.CODE = CASE WHEN T.CATEGROY = 'T1' THEN ANAL_TI
    
    		WHEN T.CATEGROY = 'T2' THEN ANAL_T2
    
            ELSE ANAL_T3
    
    	  END
    
    

    Just add the when clause as per Categroy.

    Hope this answers your question.


    • Marked as answer by Russman1981 Friday, May 27, 2011 11:36 AM
    Friday, May 27, 2011 10:53 AM

All replies

  • Try the following:

    SELECT T.SUN_DB, T.CATEGORY, T.CODE, 'ANAL_'+T.CATEGORY AS 'Tran Anal Code', T.NAME, T.PROHB_POST, MAX(GP.TRANS_DATE), COUNT(*)
    
    FROM SSRFANV AS T 
    
    LEFT JOIN dbo.AllGroupProfit AS GP
    
    ON T.SUN_DB = RIGHT(GP.TABLE_NAME,3)
    
    AND T.CODE = CASE WHEN T.CATEGROY = 'T1' THEN ANAL_TI
    
    		WHEN T.CATEGROY = 'T2' THEN ANAL_T2
    
            ELSE ANAL_T3
    
    	  END
    
    

    Just add the when clause as per Categroy.

    Hope this answers your question.


    • Marked as answer by Russman1981 Friday, May 27, 2011 11:36 AM
    Friday, May 27, 2011 10:53 AM
  • With the CASE statement you can achieve this, but the performance won't be the best.... Sample:

    SELECT ...
    FROM SSRFANV AS T
        LEFT JOIN dbo.AllGroupProfit AS GP ON ...
            AND T.CODE =
                        CASE T.CATEGORY
                            WHEN 'T2' THEN GP.ANAL_T2
                            ...
                        END
    Friday, May 27, 2011 10:58 AM
  • Many thanks, I had started to do a CASE but was told it wasn't possible within the FROM clause. Thanks, very much.


    Russell Durrant
    Friday, May 27, 2011 11:36 AM
  • HI,

    Here I need to understand few points.

    1) Tran_Date as per name looks like a DateTime column whereas currenlty it is INT. Is it by mistake or by design?

    2) In your final select statement you are not using AllGroupProfit table except for showing the max(GP.Trans_Date). so whether you really require the further join as that value you are getting by joining with T.SUN_DB = RIGHT(GP.TABLE_NAME,3condition.

    Thanks,


    Neeraj Tiwary - Please mark the post as answered if it answers your question
    Friday, May 27, 2011 11:47 AM
  • Hi,

    I is by design that the date is an integer and not DATETIME, thre are a few strange things in the design that are equally if not more odd. Makes it more interesting to try to work with.

    All sorted now I have got what I need to come out.

    Thanks,


    Russell Durrant
    Friday, May 27, 2011 12:37 PM
  • HI,

    Here in place of case expression you can use the below query. It will enhance the performance of the query.

    SELECT	T.SUN_DB, T.CATEGORY, T.CODE, 'ANAL_'+T.CATEGORY AS 'Tran Anal Code', T.NAME, T.PROHB_POST, 
    		MAX(GP.TRANS_DATE), COUNT(*)
    FROM SSRFANV AS T 
    LEFT JOIN dbo.AllGroupProfit AS GP ON T.SUN_DB = RIGHT(GP.TABLE_NAME,3) 
    AND 
    (
    	( T.CATEGORY = 'T1' AND T.Code = GP.[ANAL_T1])
    	OR
    	( T.CATEGORY = 'T2' AND T.Code = GP.[ANAL_T2])
    	OR
    	( T.CATEGORY = 'T3' AND T.Code = GP.[ANAL_T3])
    	OR
    	( T.CATEGORY = 'T4' AND T.Code = GP.[ANAL_T4])
    ...
    )

     Thanks,

    Neeraj


    Neeraj Tiwary - Please mark the post as answered if it answers your question
    • Proposed as answer by NeerajTiwary Friday, May 27, 2011 11:11 PM
    Friday, May 27, 2011 12:43 PM
  • Its my pleasure to be helpful
    Friday, May 27, 2011 12:55 PM