locked
Get data from another table RRS feed

  • Question

  • I have two table.

    Package table:
    FormType     price
    T1D:T2D      10
    D1D:D2D     5

    Transaction table:
    DRef                                                      
    T1DW20WF01750901221658A102007154A

    What I wish to do is select the DRef, substring the first three char, then see if in which packagetype to get the price.

    For example the result is:
    DRef                                                                     price
    T1DW20WF01750901221658A102007154A                10

    Thank you.
    Friday, May 29, 2009 8:14 AM

Answers

  • here is the sample script. You will have to change the table name and column name

    Select p.*,T.*From PackageTable p
    inner join 
    (select substring(dref,1,3) as dref  , price from transactionalTable) T
    on T.dref= substring(p.packagetype,1,3)
    Madhu
    MCITP, MCTS, MCDBA,MCP-- Blog : http://experiencing-sql-server-2008.blogspot.com/
    Friday, May 29, 2009 8:27 AM
  • Thank you, the FormType is T1D:T2D which is sperated by  ":".

    you need to find the string before "" : "" ? Then you may check this and change the code accordingly

    Declare

     

    @V varchar(100)

    set

     

    @v='sample:script'

    SELECT

     

    substring(@v,1,CHARINDEX(':', @v)-1)

    Madhu


    MCITP, MCTS, MCDBA,MCP-- Blog : http://experiencing-sql-server-2008.blogspot.com/
    Friday, May 29, 2009 9:35 AM

All replies

  • here is the sample script. You will have to change the table name and column name

    Select p.*,T.*From PackageTable p
    inner join 
    (select substring(dref,1,3) as dref  , price from transactionalTable) T
    on T.dref= substring(p.packagetype,1,3)
    Madhu
    MCITP, MCTS, MCDBA,MCP-- Blog : http://experiencing-sql-server-2008.blogspot.com/
    Friday, May 29, 2009 8:27 AM
  • Thank you, the FormType is T1D:T2D which is sperated by  ":".
    Friday, May 29, 2009 8:35 AM
  • Thank you, the FormType is T1D:T2D which is sperated by  ":".

    you need to find the string before "" : "" ? Then you may check this and change the code accordingly

    Declare

     

    @V varchar(100)

    set

     

    @v='sample:script'

    SELECT

     

    substring(@v,1,CHARINDEX(':', @v)-1)

    Madhu


    MCITP, MCTS, MCDBA,MCP-- Blog : http://experiencing-sql-server-2008.blogspot.com/
    Friday, May 29, 2009 9:35 AM